problem with joins

Hi

i have 2 tables called locationcode and emp.

tables are having so many records.

suppose the tables having the sample data like this:

locationcode table:

locationcode(field name)

1

2

3

emp table

empno empname locationcode (field names)

----------------------

2344 aaaa 1

2345 bbbb 1

2567 cccc 2

1234 dddd 3

16789 eeee 4

9890 fffff 4

i have to delete records from emp table the locationcode which is not matching with locationcode in locationcode table.

(in this sample data i have to delete records which are having location code as 4 from emp table)

i used joins,but i am not getting.

please help me.

Thanks.

Hello

Use left outer join

select

t1.empno, t1.empname, t1.locationcodefrom emp t1
leftouterjoin locationcode t2on t2.locationcode= t1.locationcode
where t2.locationcodeisnull

HTH

|||

hi,

i think this query can help you out.

DELETE FROM emp

where locationcode in

(SELECT locationcode from emp e, locationcode lc where e.locationcode <> lc.locationcode)

i've not tried this but i think it will work.

Happy Coding.

regards,

Muppidi.

|||

hi,

i think this query can help you out.

DELETE FROM emp

where locationcode in

(SELECT locationcode from emp e, locationcode lc where e.locationcode <> lc.locationcode)

i've not tried this but i think it will work.

Happy Coding.

regards,

Muppidi.