Inner Join Not Exists

[Midnight Marauders]

Hey All,

I am having a problem retreiving all records that are not in a second table..

I want to SELECT all records from table1 where table1.ID is not in table2

[Using MICROSOFT SQL SERVER 2005]

Here is my current code.

SELECT table1.ID_PK, table1.fName, table1.lName
FROM table1 INNER JOIN
     table2 ON table2.assoc = '17058' AND table2.status = 'APPROVED' AND table1.ID_PK <> table2.ID_FK
WHERE (table1.status <> 'PENDING')
 

This results in only retreving some records from table1. It succesfully does nto return the 1 record from table1 that is in table2 but it should return many more records from table1

I have been using SQL for some time, but usually for basic things, I have just started using more advanced queries so forgive my newbyism... I assume I need a NOT EXISTS statement, but when I tried several variations with that I got less then steller results.

FYI, in this example there is only 1 record in table2, but any suggestions should keep in mind(if possible) that the total records in table2 will probably be a min of 5000

Any help would be greatly appreciated!

Thanks.

[iburyak]

Try this:


[PHP]SELECT ID_PK, fName, lName
FROM table1 t1
WHERE status <> 'PENDING'
AND NOT EXISTS (select * from table2 where assoc = '17058' AND status = 'APPROVED' and ID_PK = t1.ID_PK )[/PHP]

Good Luck.

[Midnight Marauders]

That worked perfectly.. nice.

Thanks much!!

Try this:


[PHP]SELECT ID_PK, fName, lName
FROM table1 t1
WHERE status <> 'PENDING'
AND NOT EXISTS (select * from table2 where assoc = '17058' AND status = 'APPROVED' and ID_PK = t1.ID_PK )[/PHP]

Good Luck.

[Midnight Marauders]

Now what if I want to return all records from Table1 and I want the Table2.status value if Table1.ID_PK = Table2.ID_FK, if it doesnt i still want the record from Table1 it just wont have a Table2.status value

It seems with my code that it is pulling duplicate records. I think the output is correct asside from the duplication of course.. Nope, as i am writing this and looking at the query results I see the table2 returned values are not correct, so is this where I would use a RIGHT OUTER JOIN??

My Current Code:

SELECT *
FROM Table1 CROSS JOIN Table2
 

Any help you could provide would be greatly appreciated!!

Thanks..

[iburyak]

Try this:

[PHP]Select distinct *
FROM Table1
LEFT JOIN Table2 ON Table1.ID_PK = Table2.ID_FK[/PHP]


Good Luck.