If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > I need help selecting records in table a that are not in table b

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 );
__________________
..::Antonioj1015::..

Last edited by Antonioj1015; 02-22-13 at 09:10.
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,425
I don't see a quesiton in your post.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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::..
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,226
Isn't your query an answer?
Quote:
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?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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::..
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,226
So, please publish DDLs(CREATE TABLE statements) and sample data(INSERT statements) and your desired results from your sample data.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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 08:42.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Feb 2008
Location: Japan
Posts: 3,226
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 01:14.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On