creating a 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

General SQL Server Forums

New to SQL Server Programming

creating a query

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-07-10 : 19:02:19

Hi.

I want to create following query:

Get all of sailors name that they traveled by boats number 1 , 2 , and 3.

I wrote two query but I think they are not very good method.

Please help me to create a right query.

the diagram has 3 tables (sailors, sp as junction table, and boats)

query 1:

SELECT sailorname

FROM sailors ss

WHERE EXISTS (SELECT s.sailorname
FROM sailors s INNER JOIN

sp ON sp.sailorID=s.sailorID
WHERE sp.boatID='1' AND ss.sailorid=s.sailorid)

AND EXISTS (SELECT s.sailorname
FROM sailors s INNER JOIN

sp ON sp.sailorID=s.sailorID
WHERE sp.boatID='2' AND ss.sailorid=s.sailorid)

AND EXISTS (SELECT s.sailorname
FROM sailors s INNER JOIN

sp ON sp.sailorID=s.sailorID
WHERE sp.boatID='3' AND ss.sailorid=s.sailorid)

ORDER BY ss.sailorName

query 2:

SELECT s.sailorname

FROM sailors s INNER JOIN
sp ON sp.sailorID=s.sailorID

WHERE sp.boatID='1'

INTERSECT

SELECT s.sailorname

FROM sailors s INNER JOIN
sp ON sp.sailorID=s.sailorID

WHERE sp.boatID='2'

INTERSECT

SELECT s.sailorname

FROM sailors s INNER JOIN
sp ON sp.sailorID=s.sailorID

WHERE sp.boatID='3'

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-11 : 03:27:15

select sailorname
FROM sailors s
where sailorID IN
(select sailorID from sp where BoadID in(1, 2, 3) )

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-11 : 03:46:22

Ignore above post. I completely missed the point.

Maybe something closer to

select sailorname from sailors
inner join
(
select distinct sailorID from sp where BoatID=1
union all
select distinct sailorID from sp where BoatID=2
union all
select distinct sailorID from sp where BoatID=3
) as s
on sailors.sailorID =s .sailorID
having count(*) = 3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-11 : 04:48:43

[code]SELECT x.SailorName
FROM (
SELECT SailorID
FROM sp
GROUP BY SailorID
HAVING MAX(CASE WHEN BoatID = 1 THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN BoatID = 2 THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN BoatID = 3 THEN 1 ELSE 0 END) = 1
) AS y
INNER JOIN Sailors AS x ON x.SailorID = y.SailorID[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 14:57:46

Try them all, and compare the queries both with Actual Execution Plan and SQL Profiler.

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-07-12 : 18:43:24

quote:
Originally posted by Peso

Try them all, and compare the queries both with Actual Execution Plan and SQL Profiler.

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

How can I compare the queries by Actual Execeution Plan.
Which factor?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 18:48:03

Paste all queries to a new query window.
Click icon "Include Actual Execution Plan".
Execute the queries.
Switch window to "Execution Plan".
Now you can see the individual execution plans and their part in the total batch.

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-07-12 : 18:57:33

thanks. I have done it.
but I dont know how use it.

Pls give me a articl or advice about using Index correctly.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 19:26:49

In the header of each query, you can see the percentage of the cost of each query in the batch.
Post the queries you consider, and their percentage here.

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-07-12 : 19:38:20

-- Method 1 ----- 15%
SELECT sailorname
FROM sailors s
WHERE sailorID IN(
SELECT sailorid
FROM sp
WHERE sp.boatID=1

INTERSECT

SELECT sailorid
FROM sp
WHERE sp.boatID=2

INTERSECT

SELECT sailorid
FROM sp
WHERE sp.boatID=3)

-- Method 2 ----- 15%
SELECT sailorname
FROM sailors ss
WHERE EXISTS (SELECT *
FROM sp
WHERE sp.boatID=1
AND ss.sailorid=sp.sailorid)
AND EXISTS (SELECT *
FROM sp
WHERE sp.boatID=2
AND ss.sailorid=sp.sailorid)
AND EXISTS (SELECT *
FROM sp
WHERE sp.boatID=3
AND ss.sailorid=sp.sailorid)

-- Method 3 ---- 27%
SELECT sailorname
FROM sailors ss INNER JOIN
(
SELECT DISTINCT sailorID FROM sp WHERE BoatID=1
UNION ALL
SELECT DISTINCT sailorID FROM sp WHERE BoatID=2
UNION ALL
SELECT DISTINCT sailorID FROM sp WHERE BoatID=3
) AS s
ON ss.sailorID =s .sailorID
GROUP BY sailorname
HAVING count(*) = 3

-- Method 4 ---- 7%
SELECT SailorName FROM Sailors
WHERE SailorID IN(SELECT sailorid
FROM sp INNER JOIN
(
SELECT BoatId=1
UNION
SELECT BoatID=2
UNION
SELECT BoatID=3
) as d ON d.boatID=sp.boatID
GROUP BY sp.sailorid
HAVING count(DISTINCT sp.boatid)=3)

-- Method 5 ---- 7%
SELECT x.SailorName
FROM (
SELECT SailorID
FROM sp
GROUP BY SailorID
HAVING MAX(CASE WHEN BoatID = 1 THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN BoatID = 2 THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN BoatID = 3 THEN 1 ELSE 0 END) = 1
) AS y
INNER JOIN Sailors AS x ON x.SailorID = y.SailorID

-- Method 6 ---- 21%
SELECT sailorname FROM sailors ss inner join
sp ON sp.sailorID=ss.sailorID
WHERE sp.boatID in ('1' ,'2','3')
group by sailorname
having count (distinct sp.boatID)=3

-- Method 7 --- 8%
SELECT sailorname
FROM sailors s
WHERE NOT EXISTS(
SELECT boatID=1
UNION
SELECT boatID=2
UNION
SELECT boatID=3

EXCEPT

SELECT boatID
FROM sp
WHERE sailorID=s.sailorID
)

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-07-12 : 19:43:34

-- Peso Method ----- 47% ----
SELECT x.SailorName
FROM (
SELECT SailorID
FROM sp
GROUP BY SailorID
HAVING MAX(CASE WHEN BoatID = 1 THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN BoatID = 2 THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN BoatID = 3 THEN 1 ELSE 0 END) = 1
) AS y
INNER JOIN Sailors AS x ON x.SailorID = y.SailorID

-- My Method ----- 53% ----
SELECT sailorname
FROM sailors s
WHERE NOT EXISTS(
SELECT boatID=1
UNION
SELECT boatID=2
UNION
SELECT boatID=3

EXCEPT

SELECT boatID
FROM sp
WHERE sailorID=s.sailorID
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 19:55:15

The "Actual Execution Plan" is not the complete truth.
You should verify your results with SQL Profiler.

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-07-12 : 20:00:43

quote:
Originally posted by Peso

The "Actual Execution Plan" is not the complete truth.
You should verify your results with SQL Profiler.

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

How I visit the SQL Profiler?
What is SQL Profiler ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 20:05:33

Oh well, never mind.
It will take too long to explain.

Your query posted 07/12/2009 : 10:07:38 will also give another result than the others.
This query will give you the sailor which has travelled with BoatID 1, 2 and 3 ONLY!

My query will give you the sailors which has travelled with BoatID 1, 2 and 3 AT LEAST!

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-07-12 : 20:14:20

I test the query and give the true result.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-12 : 20:25:52

Yes, you are correct.

NOT EXISTS with an empty resultset is true.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 02:22:02

The important measure points are the READS, CPU and DURATION.
And you need to add the TSQL: Statement Completed.

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

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-07-13 : 08:50:36

Wow. It's really nice to see some follow up and a bit of effort applied. Normally it's "pleez sent me teh codez" and that's it without any effort to understand what's going on.
And I don't think mine even works without the DISTINCT.
I like #7 - very elegant.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-07-13 : 17:21:22

[code]
Performance of the queries:

QueryNumber CUP Reads Duration Cost
---------------------------------------------------------------
Method #5 15 16 3 32%
Method #4 15 16 18 33%
Method #7 15 37 6 35%
Method #2 296 32 288

[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-13 : 17:52:08

Try this one too

SELECT	s.SailorName
FROM	Sailors AS s
WHERE	EXISTS (SELECT * FROM sp AS m WHERE m.BoatID = 1 AND m.SailorID = s.SailorID)
	AND EXISTS (SELECT * FROM sp AS m WHERE m.BoatID = 2 AND m.SailorID = s.SailorID)
	AND EXISTS (SELECT * FROM sp AS m WHERE m.BoatID = 3 AND m.SailorID = s.SailorID)

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

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-07-13 : 18:34:44

quote:

Originally posted by Peso

Try this one too

SELECT	s.SailorName
FROM	Sailors AS s
WHERE	EXISTS (SELECT * FROM sp AS m WHERE m.BoatID = 1 AND m.SailorID = s.SailorID)
	AND EXISTS (SELECT * FROM sp AS m WHERE m.BoatID = 2 AND m.SailorID = s.SailorID)
	AND EXISTS (SELECT * FROM sp AS m WHERE m.BoatID = 3 AND m.SailorID = s.SailorID)

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

Dear Peso. pls take a look to post 07/12/2009 : 19:38:20



-- Method 2	
SELECT sailorname
FROM sailors ss
WHERE EXISTS (SELECT *
				FROM  sp 
				WHERE sp.boatID=1 
				AND ss.sailorid=sp.sailorid) 
	AND EXISTS (SELECT *
				FROM  sp
				WHERE sp.boatID=2 
				AND ss.sailorid=sp.sailorid) 
	AND EXISTS (SELECT *
				FROM  sp 
				WHERE sp.boatID=3 
				AND ss.sailorid=sp.sailorid)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources