Excluding Data - 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

Excluding Data

Author

Topic

dazza5621
Starting Member

7 Posts

Posted - 2009-04-27 : 08:29:45

Hi All, I have two tables computers and software and need to select a computer that has either app1, app2 or app3 but not app4, I have the first bit but can't seem to get the exclusion, this is what I have so far:

SELECT computers.guid, computers.name, software.name
FROM software
INNER JOIN computers ON software.guid = computers.guid
WHERE software.name IN ('app1','app2','app3')

Thanks

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 08:35:06

You have bad table design, look into normalization. You need a junction table connecting computers and software together.

Your query should work? It excludes app4?

dazza5621
Starting Member

7 Posts

Posted - 2009-04-27 : 08:40:39

Unfortunatley, it is not my design so I have to work with it, would I be best creating a temp table and working with that?

Thanks

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 08:42:05

SELECT computers.guid, computers.name, software.name
FROM software
INNER JOIN computers ON software.guid = computers.guid
WHERE software.name IN ('app1','app2','app3')

Your query should ALREADY exclude app4 (due to the WHERE software.name IN ('app1','app2','app3') clause).

dazza5621
Starting Member

7 Posts

Posted - 2009-04-27 : 08:42:05

Maybe I didn't explain correctly I want a computer if it has app1, app2 or app3 but not if it has app4

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 08:43:56

[code]SELECT computers.guid, computers.name, software.name
FROM software
INNER JOIN computers ON software.guid = computers.guid
WHERE software.name IN ('app1','app2','app3')

SELECT c.GUID,
c.Name,
s.Name
FROM Computers AS c
INNER JOIN Software AS s ON s.GUID = c.GUID
INNER JOIN (
SELECT GUID
FROM Software
GROUP BY GUID
HAVING MAX(CASE WHEN Name IN ('app1', 'app2', 'app3') THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN Name = 'app4') THEN 1 ELSE 0 END) = 0
) AS d ON d.GUID = s.GUID[/code]

E 12°55'05.63"
N 56°04'39.26"

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 08:46:27

SELECT computers.guid, computers.name, software.name
FROM software
INNER JOIN computers ON software.guid = computers.guid
WHERE computers.guid NOT IN (SELECT software.guid FROM software WHERE software.guid = computers.guid AND software.name = 'app4');

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 08:54:04

That will exclude App4.
Where are App1 or App2 or App3 included?

E 12°55'05.63"
N 56°04'39.26"

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 08:56:32

WHERE computers.guid NOT IN

This will exclude all computers that contain App4.

quote:
Originally posted by dazza5621

Maybe I didn't explain correctly I want a computer if it has app1, app2 or app3 but not if it has app4

EDIT: I was assuming there was only 4 app kinds.
SELECT computers.guid, computers.name, software.name
FROM software
INNER JOIN computers ON software.guid = computers.guid
WHERE computers.guid NOT IN (SELECT software.guid FROM software WHERE software.guid = computers.guid AND software.name = 'app4') AND software.name IN ('app1','app2','app3');

dazza5621
Starting Member

7 Posts

Posted - 2009-04-27 : 09:07:34

Thanks Whitefang the last post did the trick

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 09:10:54

[code]DECLARE @Software TABLE
(
[guid] INT,
Name VARCHAR(10)
)

INSERT @Software
SELECT 1, 'app1' UNION ALL
SELECT 2, 'app1' UNION ALL
SELECT 2, 'app3' UNION ALL
SELECT 3, 'app1' UNION ALL
SELECT 3, 'app3' UNION ALL
SELECT 3, 'app2' UNION ALL
SELECT 3, 'app4' UNION ALL
SELECT 1, 'app4'

DECLARE @Computers TABLE
(
[guid] INT,
Name VARCHAR(10)
)

INSERT @Computers
SELECT 1, 'Comp1' UNION ALL
SELECT 2, 'Comp2' UNION ALL
SELECT 3, 'Comp3' UNION ALL
SELECT 4, 'Comp4'

-- Peso (9 reads)
SELECT c.GUID,
c.Name,
s.Name
FROM @Computers AS c
INNER JOIN @Software AS s ON s.GUID = c.GUID
INNER JOIN (
SELECT GUID
FROM @Software
GROUP BY GUID
HAVING MAX(CASE WHEN Name IN ('app1', 'app2', 'app3') THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN Name = 'app4' THEN 1 ELSE 0 END) = 0
) AS d ON d.GUID = s.GUID

-- WhiteFang (24 reads)
SELECT c.guid,
c.name,
s.name
FROM @software AS s
INNER JOIN @computers AS c ON s.guid = c.guid
WHERE c.guid NOT IN (SELECT x.guid FROM @software as x WHERE x.guid = c.guid AND x.name = 'app4')
AND s.name IN ('app1', 'app2', 'app3')[/code]

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 09:11:39

dazza5621, why would you want to use three times as much reads from the database than the suggestion I made?

E 12°55'05.63"
N 56°04'39.26"

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-27 : 09:14:54

Funny when the Execution Plan shows that your query takes 74% more time and resources to complete and mine takes only 26%.

dazza5621, why would you want to use 74% more time and resources from the database than the suggestion I made?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 09:21:32

75% (Peso part in batch) vs 25% (WhiteFang in batch) is 200% more.
Learn your percentage calculations.

And you're wrong about the resources. SQL Profiler says I am using only 9 reads from the database and you are using 24 reads.
Reads are the expensive operation in a query.

Populate the sample data with 5000 records and try again.
I did and got half the DURATION as WhiteFang, and a third of the reads.
Still Batch percentage in SSMS said 65% for my suggestion and 35% for WhiteFang.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 09:50:28

And you can try this, which has more reads than WhiteFang but a quarter the DURATION

SELECT		GUID,
		ComputerName,
		SoftwareName
FROM		(
			SELECT		c.GUID,
					c.Name AS ComputerName,
					s.Name AS SoftwareName,
					MAX(CASE WHEN s.Name = 'App4' THEN 1 ELSE 0 END) OVER (PARTITION BY s.GUID) AS recID
			FROM		#Computers AS c
			INNER JOIN	#Software AS s ON s.GUID = c.GUID
			WHERE		s.Name IN ('app1', 'app2', 'app3', 'app4')
		) AS d
WHERE		recID = 0

So I guess which method you should use is dependant on how many records you have in your table, and which indexes are present.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-27 : 09:55:13

WhiteFang, you also are too reliant on the batch percentage.

See this example

;WITH Yak (i)
AS (
	SELECT 0

	UNION ALL

	SELECT	i + 1
	FROM	Yak
	WHERE	i < 10
)
SELECT	*
FROM	Yak

SELECT	0 AS i UNION ALL
SELECT	1 AS i UNION ALL
SELECT	2 AS i UNION ALL
SELECT	3 AS i UNION ALL
SELECT	4 AS i UNION ALL
SELECT	5 AS i UNION ALL
SELECT	6 AS i UNION ALL
SELECT	7 AS i UNION ALL
SELECT	8 AS i UNION ALL
SELECT	9 AS i UNION ALL
SELECT	10 AS i UNION ALL

First query has 103 reads and 46 ms duration but only 36% of the batch.
Second query has 0 reads and 6 ms but also 64% of batch.

How do you correlate that percentage?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources