Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2009
    Posts
    18

    Question I need help selecting records in table a that are not in table b

    I need to select the records that are in table_a but not in table_b, the thing is that I also need to select the records in table_a that dont have birthdate and the records on table_b that doesnt have answers, something like this:

    SELECT pacient.* FROM pacient
    LEFT JOIN test
    ON pacient.idpacient = test.idpacient
    WHERE test.idpacient IS NULL
    OR pacient.date_birth IS NULL
    OR test.answer = 0

    To clear it all out I need the records of the persons that dont have birthdate, or are not in table_b or that their answer is 0

    HOW CAN I SELECT the records that are in table_a but not in table_b, or the records in table_a that dont have birthdate and the records on table_b that dont have answers??

    Table pacient
    id | name | datebirth
    1 | Jose | 1989-10-15
    2 | Mark | 1985-10-15
    3 | Maria | 1984-10-15
    4 | Liz | NULL
    5 | Joe | 1990-01-25

    Table test
    id | id_pacient | answer
    1 | 1 | 1
    2 | 3 | 5
    3 | 4 | 1
    4 | 5 | 0

    The query I want to make should display the following pacients:
    2 Mark (Who doesnt appear in the test table)
    4 Liz (Who doesnt have a birth date even though she has an answer in the test table);
    5 Joe (Who has 0 in the answer column in the test table );
    Last edited by Antonioj1015; 02-22-13 at 10:10.
    ..::Antonioj1015::..

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,971
    I don't see a quesiton in your post.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2009
    Posts
    18
    HOW CAN I SELECT the records that are in table_a but not in table_b and the records in table_a that dont have birthdate and the records on table_b that dont have answers??
    ..::Antonioj1015::..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Isn't your query an answer?
    SELECT pacient.* FROM pacient
    LEFT JOIN test
    ON pacient.idpacient = test.idpacient
    WHERE test.idpacient IS NULL
    OR pacient.date_birth IS NULL
    OR test.answer = 0
    What isuues were in your query?

  5. #5
    Join Date
    Aug 2009
    Posts
    18
    It doesnt select all the persons with the restrictions in the query, it will list some but there's a few reords that dont have birthdates and its not including them
    ..::Antonioj1015::..

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    So, please publish DDLs(CREATE TABLE statements) and sample data(INSERT statements) and your desired results from your sample data.

  7. #7
    Join Date
    Aug 2009
    Posts
    18
    Table pacient
    id | name | datebirth
    1 | Jose | 1989-10-15
    2 | Mark | 1985-10-15
    3 | Maria | 1984-10-15
    4 | Liz | NULL
    5 | Joe | 1990-01-25

    Table test
    id | id_pacient | answer
    1 | 1 | 1
    2 | 3 | 5
    3 | 4 | 1
    4 | 5 | 0

    The query I want to make should display the following pacients:
    2 Mark (Who doesnt appear in the test table)
    4 Liz (Who doesnt have a birth date even though she has an answer in the test table);
    5 Joe (Who has 0 in the answer column in the test table );
    Last edited by Antonioj1015; 02-22-13 at 09:42.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    So, try by modifying slightly column names in your query, like...
    Code:
    SELECT pacient.*
     FROM  pacient
     LEFT  JOIN
           test
      ON   pacient.id = test.id_pacient
     WHERE test.id_pacient   IS NULL
       OR  pacient.datebirth IS NULL
       OR  test.answer = 0
    ;
    Last edited by tonkuma; 02-23-13 at 02:14.

Posting Permissions

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