Left join without condition

Question

Left join without condition

shenyjoy

SSC-Addicted

Points: 421
More actions
July 24, 2008 at 5:31 am

#191521

Hi,
I have two tables. Table_A and Table_B.
I have the folowing records in Table_A
ID COURSE ST FT WK Faculty
---------------------------------------
111 VVV 10:00 12:00 2 4521216
111 VVV 10:00 12:00 4 4521216
111 VVV 1100 12:00 3 2323233
111 VVV 1100 12:00 1 4521216
I have the folowing records in Table_B
ID COURSE ST FT WK Faculty
---------------------------------------
111 VVV 10:00 12:00 2 4521216
111 VVV 10:00 12:00 4 4521216
111 VVV 1100 12:00 3 2323233
I have four records in table A and 3 in table B,
Originally in the table there are 5439 rows in Table A and 5223 in Table B. That means I need to get the missing record from Table A..
How can I get that. I am trying a LEFT OUTER JOIN and it gives me all the values where a.id=b.id.
But as u see... how can I capture that 4th record in Table A which is not there in Table _B
The is really urgent. Will appreciate kind help. Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

dcorbitt Ten Centuries Points: 1006 More actions · Answer 1

Is there a unique index on the tables? If so, what fields comprise it? (Or, is there a way to tell if a record is in both files without comparing every column?)

GSquared SSC Guru Points: 260824 More actions · Answer 2

Am I mistaken, or did I answer this same post in another forum?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

David Leaver Mr or Mrs. 500 Points: 599 More actions · Answer 3

For simplicity, try the EXCEPT operator

SELECT * FROM Table_A

EXCEPT

SELECT * FROM Table_B

shenyjoy SSC-Addicted Points: 421 More actions · Answer 4

Thanks David,

That worked perfectly. It was so simple and I tried it but I did not have tables with same columns and there were additional columns in one select stmt.

Thank you for your help. This works 🙂