چالش در ایجاد یک کوئری

[محمد سلیم آبادی]

سلام دوستان.
یک کوئری می خواهم ایجاد کنم به این شکل:

نام تمام ملبانانی که با قایق های شماره 1 و 2 و 3 به مسافرت رفته اند را بدست آورید؟

دو راه به ذهنم رسید ولی باز هم روش های خوبی نیستند.
این دو روش را مطرح می کنم و از تمام دوستانی که با روش های بهتری آشنا هستند می خواهم که مرا راهنمایی کنند.

بانک اطلاعاتی ام به این شکل است:
http://www.barnamenevis.org/forum/attachment.php?attachmentid=32994&stc=1&d=1247514847


روش اول:



-- Method 1
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')
ORDER BY sailorName



روش دوم:




-- 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)
ORDER BY ss.sailorName

[HDDSoft2001]

سلام
این که خیلی ساده است !




select s.sailorsName
from sailors s innerjoin sp
where (s.SailorID = sp.SailorID) and (sp.BoatID='1' Or sp.BoatID='2' Or sp.BoatID='3')





امیدوارم مفید باشه :لبخندساده:

[محمد سلیم آبادی]

سلام
این که خیلی ساده است !
امیدوارم مفید باشه :لبخندساده:

سلام.
کوئری شما یعنی اینکه تمام ملبانانی که با یکی از این سه قایق مسافرت کرده اند را برگردان
در صورتی که من میخواهم تمام ملبانانی که با هر سه قایق سفر کرده باشند را نشان بده.

بهتره یک بار دیگر با دقت سوال رو بخوانید.

[محمد سلیم آبادی]

سلام.
اینگار خبری نیست باید خودم دست بکار بشم.

با استفاده از UNION باز هم مسئله حل می شود ولی هنوز بهینه نشده است.



-- Method 3
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
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)
ORDER BY SailorName

[محمد سلیم آبادی]

یک راه دیگه که به من پیشنهاد شده:


-- Method 5
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
ORDER BY x.sailorname

[محمد سلیم آبادی]

سرانجام پس از پیگیری های متعدد (در فروم های انگلیسی) ساده ترین روش بدست آمد.



-- Method 6
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

[محمد سلیم آبادی]

در اینجا یک روش کاملا خلاقانه را مطرح می کنم که با استفاده از عملگر های زیر ساخته شده است:
NOT EXISTS - UNION - EXCEPT




-- Method 7
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
)
ORDER BY sailorName