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 a SELECT query

Author  Topic 

jarrette
Starting Member

4 Posts
Posted - 2008-12-03 : 14:59:50
I need to do the following...


SELECT 
Description as DispositionName,
(SELECT Count(*) FROM Calls c WHERE c.DispositionID = ??? AND DateLogged > '10/3/2008' AND DateLogged <= '12/3/2008')	
FROM Dispositions d


where ??? is the dispositionID of the current row...

.Net Developer

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-03 : 15:16:06
SELECT
Description as DispositionName,
(SELECT Count(*) FROM Calls c WHERE c.DispositionID = d.DispositionID AND c.DateLogged > '10/3/2008' AND c.DateLogged <= '12/3/2008')
FROM Dispositions d


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

jarrette
Starting Member

4 Posts
Posted - 2008-12-03 : 15:18:07
damnit, DUH, thanks Peso

.Net Developer
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts
Posted - 2008-12-03 : 16:34:10
something like this might work a bit faster, but the logic might need a bit of tweaking:

SELECT
	d.Description as DispositionName,
	COUNT(c.DispositionID)
FROM
	Dispositions d
LEFT OUTER JOIN
	Calls c
	ON
		c.DispositionID = d.DispositionID
		AND
		c.DateLogged BETWEEN '10/3/2008' AND '12/3/2008'
GROUP BY
	d.Description

The advantage here is there is no subquery which can under certain circumstances cause a performance hit.



SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2008-12-03 : 16:47:51
Take this
c.DateLogged BETWEEN '10/3/2008' AND '12/3/2008'
out of ON and put it into the WHERE clause.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts
Posted - 2008-12-03 : 16:52:37
That would force an INNER JOIN on Calls, which would eliminate the 0 counts of Dispositions.


SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2008-12-03 : 17:03:20
No this would shrink the "superset" and when i am thinking about it...
the left join is completely useless because the OP would like to count only existing matches.
quote:
SELECT Count(*) FROM Calls c WHERE c.DispositionID = d.DispositionID AND c.DateLogged > '10/3/2008' AND c.DateLogged <= '12/3/2008'

The left join blows up the data that is to examine from the query processor.

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-12-03 : 17:17:52
The left join gives the correct results though:
--Setup
DECLARE @Dispositions TABLE (DispositionID INT, Description VARCHAR(100))

INSERT @Dispositions
SELECT 1, 'Dispo 1'
UNION ALL SELECT 2, 'Dispo 2'
UNION ALL SELECT 3, 'Dispo 3'
UNION ALL SELECT 4, 'Dispo 4'

DECLARE @Calls TABLE (DispositionID INT, DateLogged DATETIME)

INSERT @Calls
SELECT 1, '20081001'
UNION ALL SELECT 1, '20081030'
UNION ALL SELECT 1, '20081130'
UNION ALL SELECT 3, '20081130'
UNION ALL SELECT 3, '20081230'

-- Orginal
SELECT 
Description as DispositionName,
(SELECT Count(*) FROM @Calls c WHERE c.DispositionID = d.DispositionID AND c.DateLogged > '10/3/2008' AND c.DateLogged <= '12/3/2008') 
FROM @Dispositions d 
-- Results
--DispositionName	(No column name)
--Dispo 1	2
--Dispo 2	0
--Dispo 3	1
--Dispo 4	0

-- Query 1 - LEFT OUTER JOIN
SELECT
	d.Description as DispositionName,
	COUNT(c.DispositionID) AS DispositionCount
FROM
	@Dispositions d
LEFT OUTER JOIN
	@Calls c
	ON c.DispositionID = d.DispositionID
	AND c.DateLogged BETWEEN '10/3/2008' AND '12/3/2008'
GROUP BY
	d.Description

-- Results
--DispositionName	DispositionCount
--Dispo 1	2
--Dispo 2	0
--Dispo 3	1
--Dispo 4	0


-- Query 2 -- INNER JOIN
SELECT
	d.Description as DispositionName,
	COUNT(c.DispositionID) AS DispositionCount
FROM
	@Dispositions d
INNER JOIN
	@Calls c
	ON c.DispositionID = d.DispositionID
WHERE
	c.DateLogged BETWEEN '10/3/2008' AND '12/3/2008'
GROUP BY
	d.Description

-- Results
--DispositionName	DispositionCount
--Dispo 1	2
--Dispo 3	1
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2008-12-03 : 17:23:24
Yes. I have never had a doubt.
But I think it is not a good idea to say something like "use a left join for better performance" because in this case it isn't.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts
Posted - 2008-12-03 : 17:23:34
Not true. The original query will return zero counts; an INNER JOIN operation will exclude zero counts. I submit the following code for your review:

DECLARE @Dispositions TABLE
	(Description VARCHAR(10));
	
DECLARE @Calls TABLE
	(DateLogged DATETIME,
	DispositionID VARCHAR(10));
	
INSERT INTO @Dispositions(Description)
SELECT 'A'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'C'
UNION ALL
SELECT 'D'

INSERT INTO @Calls(DateLogged, DispositionID)
SELECT '10-01-2008', 'A'
UNION ALL
SELECT '10-04-2008', 'B'
UNION ALL
SELECT '10-05-2008', 'B'
UNION ALL
SELECT '11-10-2008', 'C'
UNION ALL
SELECT '12-01-2008', 'D'
UNION ALL 
SELECT '12-05-2008', 'D'

--This was the accepted solution
SELECT
Description as DispositionName,
(SELECT Count(*) FROM @Calls c WHERE c.DispositionID = d.Description AND c.DateLogged > '10/3/2008' AND c.DateLogged <= '12/3/2008')
FROM @Dispositions d

--This was my solution
SELECT
	d.Description as DispositionName,
	COUNT(c.DispositionID)
FROM
	@Dispositions d
LEFT OUTER JOIN
	@Calls c
	ON
		c.DispositionID = d.Description
		AND
		c.DateLogged BETWEEN '10/3/2008' AND '12/3/2008'
GROUP BY
	d.Description

--This was your suggestion	
SELECT
	d.Description as DispositionName,
	COUNT(c.DispositionID)
FROM
	@Dispositions d
LEFT OUTER JOIN
	@Calls c
	ON
		c.DispositionID = d.Description
WHERE 
	c.DateLogged BETWEEN '10/3/2008' AND '12/3/2008'
GROUP BY
	d.Description

--This is you suggestion using an INNER JOIN
SELECT
	d.Description as DispositionName,
	COUNT(c.DispositionID)
FROM
	@Dispositions d
INNER JOIN
	@Calls c
	ON
		c.DispositionID = d.Description
WHERE 
	c.DateLogged BETWEEN '10/3/2008' AND '12/3/2008'
GROUP BY
	d.Description

As you can see if you run this, the first query was the accepted answer; the second query was my original response (sans a typo). The third query moved the ON clause to the WHERE clause, which produced identical results as the 4th query, which is an inner join operation vs. an outer join. The first two produce identical results. The last two do not.



SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts
Posted - 2008-12-03 : 17:28:37
quote:
I think it is not a good idea to say something like "use a left join for better performance" because in this case it isn't


A left join operation is generally better performance that a subselect. Do you disagree?


SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2008-12-03 : 17:32:11
OK in consideration of zero-counts you are right .

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts
Posted - 2008-12-03 : 17:36:25
quote:
A left join operation is generally better performance that a subselect. Do you disagree?

Not in general because the optimizer is good enough to make it perform very well.
Subselects are today not generally bad.

Webfred



No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts
Posted - 2008-12-03 : 17:52:29
Well I think that this is a textbook case where a left join operation is called and optimized for, but I have no intention of setting up a performance analysis between subselects and left joins. I think the best solution here is to agree to disagree. :)


SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-04 : 01:24:56
What if DispositionID is not unique? Then LEFT JOIN will return the wrong count number.

E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts
Posted - 2008-12-04 : 09:53:40
The original query (and your original solution) would have similarly skewed results at that point, I believe. Can you give a dataset where the results would be different?


SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-12-04 : 10:04:54
We have no idea about the DDL. Is DispositionID unique?
DECLARE @Dispositions TABLE
	(
		DispositionID INT
	)

INSERT	@Dispositions
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	4

DECLARE @Calls TABLE
	(
		DispositionID INT
	)

INSERT	@Calls
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	3 UNION ALL
SELECT	4

-- Peso
SELECT	d.DispositionID,
	(SELECT COUNT(*) FROM @Calls AS c WHERE c.DispositionID = d.DispositionID) 
FROM	@Dispositions AS d

-- jholovacs
SELECT		d.DispositionID,
		COUNT(c.DispositionID)
FROM		@Dispositions AS d
LEFT JOIN	@Calls AS c ON c.DispositionID = d.DispositionID
GROUP BY	d.DispositionID


E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

jholovacs
Posting Yak Master

163 Posts
Posted - 2008-12-04 : 10:20:58
hmmm.... very good. You're right, I had assumed DispositionID was unique. If it is not, the left join version will require some sort of unique identifier to factor in, unless the Dispositions table is filtered beforehand.

I think the DispositionID field being unique is a fair assumption, but you're quite right that the qualification needs to be made.


SELECT TOP 1
w.[name]
FROM
dbo.women w
INNER JOIN
dbo.inlaws i
ON
i.inlaw_id = w.parent_id
WHERE
i.net_worth > 10000000
AND
i.status IN ('dead', 'dying')
AND
w.husband_id IS NULL
ORDER BY
w.hotness_factor DESC
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site