help with the self join on table

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

General SQL Server Forums

New to SQL Server Programming

help with the self join on table

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-12-08 : 14:20:47

I have a table called Auditlog in which i have fields
pkgid,queueid,userid,audittype,auditstamp

I need to write the self join on this table
so that when audittype,queueid,pkgid are same for two rows and audittype = 15
i only need to get that rows in my selfjoin but when i tried doing so i am getting more columns in the output.

if for ex: pkgid queueid,audittype
100, 5 15
100 5 15
101 10 5
102 20 15
102 20 15

output should be 100 5 15
100 5 15
102 20 15
102 20 15

but i am getting
100 5 15
100 5 15
100 5 15
100 5 15
102 20 15
102 20 15

Please helpme out!!

Thanks,
reacha

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-08 : 16:44:03

select t.*
from tbl t
join
(
select pkgid, queueid
from tbl
where audittype = 15
group by pkgid queueid
having count(*) = 2
) a
on t.pkgid = a.pkgid
and t.queueid = a.queueid
and t.audittype = 15

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

reacha
Starting Member

49 Posts

Posted - 2010-12-08 : 17:59:29

i got the correct output..

Thanks very much for your help

Thanks,
reacha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources