Self join - 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

General SQL Server Forums

New to SQL Server Programming

Self join

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2008-06-13 : 09:58:42

Hi, I came accross that code. It seems it is a self join, but I am not sure what it does.

SELECT DISTINCT HPD1.Contact_Company AS am_label1,
(
SELECT COUNT(*)
FROM HPD_Help_Desk HPD2
WHERE HPD2.Status < 5 AND
DATEADD("s", HPD2.submit_date, '1/1/1970') >=
CAST(CONVERT(char(8),GETDATE(),112) AS datetime AND
HPD2.Contact_Company = HPD1.Contact_Company
) AS NumberOfIncidents

FROM HPD_Help_Desk HPD1

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-13 : 10:10:16

It is correlated subquery with Self Join.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mukhan85
Starting Member

46 Posts

Posted - 2008-06-13 : 10:11:35

But what it does... can anybody explain it.
Thank you.

quote:
Originally posted by harsh_athalye

It is correlated subquery with Self Join.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-13 : 10:27:52

Its returning distinct values of Contact_Company field from table along with count of records having Status <5 and submitdate >= today for each Contact_Company

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-06-13 : 10:39:27

There is no need of SELF join and subquery.

You can write it like this:

Select Contact_Company AS am_label1,
count(*) AS NumberOfIncidents
from  HPD_Help_Desk
WHERE 
Status < 5 AND 
DATEADD(second, submit_date, '1/1/1970') >= CAST(CONVERT(char(8),GETDATE(),112) AS datetime
Group by Contact_Company

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources