INNER JOINS

[vinceja]

Here is my delema:

Master Table: Key is SS#
2nd Table: Key is SS#

How do I display the SSN's from 2nd Table if they're not on the Master Table? I think its done with a JOIN...

Thanks for your help !!

JV

 

[nicsin]

hi JV,

try this


SELECT a.SSN
FROM <child> as a
WHERE a.SSN not in
(SELECT b.SSN
FROM <master> INNER JOIN b ON <master>.SSN = b.SSN)

 

[MFritts253]

JV,
I think you might want to you a UNION ALL if you want to list all of the SSN's on both tables,
Some thing like this,
SELECT Employee.First AS Name, &quot;First Name&quot; as TYPE
FROM Employee;
UNION ALL SELECT Employee.Last, &quot;Last Name&quot;
FROM Employee
ORDER BY 1;
Let me know how this goes VJ
Later,
Fritts

 

[MFritts253]

VJ the Example i left is incorrect but i do have an answer for you i just need to find the example.
Sincerely,
Fritts

 

[nicsin]

MFritts253,

when you use UNION the structure of the two tables has to be identical and as you said UNION ALL returns ALL records on both tables, which will probably lead to duplication.

JV,

The query in my previous posting returns only the SSNs that are not present in the master table. If I didn't get it right and you want to display ALL the SSNs from the child then use this:

SELECT a.SSN
FROM <child> as a left join <master> b on a.SSN = b.SSN

 

[nicsin]

oops, that should be SELECT a.SSN
FROM <child> a left join <master> b on a.SSN = b.SSN

 

[danvlas]

Select Distinct SSN From SecondTable Where SSN Not In (Select SSN From MasterTable)

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html