Join

[SweetDot]

Hi,

I need to find out all the records that do not qualify for an Inner join, how can I do that?
I have a query that does the inner join and puts it into a table, but I need to know all the other records that have not been inserted into a table. Please help.

thanks

 

[checkai]

I believe you can do a RIGHT JOIN to get that data...

dlc

 

[SweetDot]

sorry, just to clarify...right join gives you all the records that don't qualify the condition? i only want the ones that don't qualify?

 

[checkai]

A right join I believe will give you everything from table2 that is not in table1

Select table2.*
from table1 RIGHT JOIN table2 on table1.field1 = table2.field1

Try it...I think that should work...

dlc

 

[SQLSister]

Left joins are preferable. But either right ot left joins will work if you put the correct where clause in. Suppose you want the records in table 1 which do not have a match in table2.

Select table1.* from table1 left join table2 
On table1.id = table2.id 
Where table2.id is null

 

[SweetDot]

thank you

 

[SweetDot]

another question ......please help

before the inner join i have the following records:
i have a record with two important fields.... Ter_TL_ID and
BOUND_LOG_ID...
TL_ID is the table id of that record, and BOUND ID is the id that this
current record is connected to, i.e.
Record Ter_TL_ID Bound_Log_ID
1 00034 00045
2 00045 00034

since i did the inner join to get them linked together...
i get duplicate records...but they're not so distinct, how can i get rid of
the duplicates?

they look like this now...
Record Table1.Ter_TL_ID Table1.Bound_Log_ID Table2.Ter_TL_ID
Table2.Bound_Log_ID
1 00034 00045
00045 00034
2 00045 00034
00034 00045

i wanna remove either record 1 or record 2 since they are essentially the same thing......

am i making sense??