Help in Query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help in Query...

Author

Topic

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-10-04 : 06:05:09

Hello All,

I do have four columns [A, B, C, D] in say my 'Emp' Table.

And i do have another table called 'dept' and that table is also having that same four columns, i mean [A, B, C, D].

Now i want to find certain records in Table Dept which not exists in table called 'EMP'....

-- Thanks
Prashant Hirani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 06:29:08

[code]SELECT A,B,C,D
FROM
(SELECT A,B,C,D,1 AS Cat
FROM Emp
UNION ALL
SELECT A,B,C,D,2 AS Cat
FROM dept)t
GROUP BY A,B,C,D
HAVING SUM(CASE WHEN Cat=1 THEN 1 ELSE 0 END)=0[/code]

hirani_prashant
Yak Posting Veteran

93 Posts

Posted - 2008-10-04 : 07:04:27

Thanks for the reply.

Now if i want to remove that rows from dept table then what query i should have to use.

Thanks
Prashant Hirani

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-04 : 07:09:52

quote:
Originally posted by hirani_prashant

Thanks for the reply.

Now if i want to remove that rows from dept table then what query i should have to use.

Thanks
Prashant Hirani

DELETE d
FROM dept d
LEFT JOIN Emp e
ON e.A=d.A
AND e.B=d.B
AND e.C=d.C
AND e.D=d.D
WHERE e.A IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources