problem with sql query.

Hi ya,

I have two tables. One is the temporary table called tbl_temp and the other is called tbl_empDetails.
I want to run a query that would bring me the records of tbl_temp where there is no corresponding ID present in tbl_empDetails, meaning it shud bring only records where a corresponding records is not available in tbl_empDetails. I tried it but this query doesn't work, it is bringing me the records which are there in tbl_empdetails. How can i do it?

SELECTDISTINCT tbl_temp.SID, tbl_temp.fname, tbl_temp.lname, tbl_temp.mail, tbl_temp.tel, tbl_temp.mobile, tbl_temp.office

FROM tbl_temp, tbl_empDetails where tbl_empDetails.adSID != tbl_temp.SID

ORDERBY tbl_temp.lname

Try this

SELECTDISTINCT *

FROMtbl_empDetails

where not tbl_empDetails.adSID in (select SID from tbl_temp)

|||

thank you very very much, you saved me lot of work.

Can you care to explain what you have done with the query and what I was doing wrong?

Just in case if i got any similar problem again.

Cheers

|||

Table 1

KeyValue

1

2

3

Table 2

KeyValue

1

2

If you use "where tbl_empDetails.adSID = tbl_temp.SID" you will get 2 record. (1,1) (2,2)

if you use "where tbl_empDetails.adSID != tbl_temp.SID" you will get 4 records. (1,2) (2,1) (3,1) (3,2)

So. I use sub-query to solve it !

|||

SELECTDISTINCT a.*

FROMtbl_temp a

LEFT JOIN tbl_empDetails b

ON a.SID = b.adSID

Where b.asSID IS NULL

This will run much faster than a sub-query if your tables are large...

Brad