SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get record from table1 which not exist in table2

    hi to all,

    I have two table for example

    table a with one field a_id and second table b with three fields one is b_id,a_id,c_id

    Now i want to get all records of table a whose a_id and a constant c_id is not in table b

    i can get records of table a whose has a_id not in table b but i want to check the combination of a_id and c_id where c_id is any value for example constant.


    For example

    table a has records

    a_id
    1
    2
    3
    4

    table b has records
    b_id a_id c_id
    1 1 10
    2 3 10


    i want to get records form a
    a_id
    2
    4

    because these two records not exist in table b with c_id 10

    get records from a where a.a_id not equal to b.a_id and b.c_id not equal to 10



    regards.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Those two records don't exist in table b, so c_id does not exist. You can't impose a constraint on a value that doesn't exist. It's not equal to 10, but it's also not equal to 9, or 8, or anything.

    So how does the nonexistant c_id come into the equation?

    If table b had a row

    3 2 10

    Would you want record 2 then?

    If table b had a row

    3 2 9

    Would you want record 2 then?

    If table b had row

    3 3 9

    Would you now want record 3 as well?

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for reply,

    If table b had a row

    3 2 10

    Would you want record 2 then?

    yes i want to get record 2 in this case

    If table b had a row

    3 2 9

    Would you want record 2 then?

    no because in our query c_id will be 10 but c_id will change

    If table b had row

    3 3 9

    Would you now want record 3 as well?

    no same case with record 2

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Your requirements are still in conflict.

    Record 3 has a row in table A and B with CID 10, but you don't want 3, while you said you'd want record 2 if it had the same entry.

    Code:
    Table A:
    
    a_id
    ----
    1
    2
    3
    4
    5
    
    Table B:
    
    b_id | a_id | c_id
    -----------------
    1    | 1    | 10
    2    | 2    | 9
    3    | 2    | 10
    4    | 3    | 9
    5    | 4    | 9
    Which rows do you want and why?

    Or I can take a guess that you don't mean what you're saying:

    Code:
    SELECT
      a.a_id
    FROM
      a
    LEFT OUTER JOIN
      b
    ON
      a.a_id = b.b_id
    WHERE
      b.a_id IS NULL
      OR
      (b.a_id IS NOT NULL AND b.c_id = 10)
    Which would give you all rows of A which have no match in B, or do have a match with c_id 10.

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for guide me,

    my requirement was
    Code:
     SELECT *
    
    FROM a
    
    LEFT JOIN b ON a.a_id = b.a_id
    
    WHERE b.a_id IS NULL
    
    OR (
    b.a_id IS NOT NULL
    AND b.c_id !=10
    )
    LIMIT 0 , 30
    thanks again

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have come across proble when table b has many entry so it return more than one time the result

    so my requirement is

    I have two table with following field and data
    Table a
    a_Id-----name
    1--------A
    2--------B
    3--------C
    4--------D

    Table b
    b_Id-----name-----C_id
    1--------A---------10
    2--------B---------10
    3--------B---------9
    4--------B---------8
    5--------C---------7
    6--------C---------6

    I want to make a query which return all records from Table a who has no record in table b with combination of c_id which we suppose to 10
    Sample query will be without join
    Select * from a where a.name not equal to b.name and b.c_id not equal to 10;
    But record from table a come once
    Our final result will be
    a_Id ----- name
    3--------- C
    4----------D

    In table b we see name B come three time once with 10 and two time with 8,9 so I need if record come with B and c_id 10 then from table a does not show the record B.Record with name C in table b come twice but not with c_id 10 so it should come in final result once .D does not have entry in table b so I should come in final result

    regards

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    Code SQL:
    SELECT
         t1.a_id AS id
         ,t1.name AS name
      FROM
         a AS t1
     WHERE
         t1.name 
       NOT 
        IN
          (
          SELECT
               DISTINCT t1.name
            FROM
                b AS t1
           WHERE
               t1.c_id = 10              
          )

  8. #8
    SitePoint Zealot
    Join Date
    Sep 2008
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks work perfect.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •