Is there a mysql bit similar to IN(), but something more like "IN ALL()"?

[ultrus]

I'm attempting to retrieve a list of solar systems in the fictitious EVE Online galaxy that contain ALL of the requested types of ice. Fun aye? I'm having brain shortage on the proper structure for this.

 

Getting systems for one type of ice is pretty straight forward:

 

SELECT s.solarSystemID
FROM mapRegionIceTypes i
LEFT JOIN invTypes t ON i.typeID = t.typeID
LEFT JOIN mapSolarSystems s ON s.regionID = i.regionID
WHERE  i.maxSecurity >= s.security 
AND i.minSecurity <= s.security 
AND i.typeID = 17976

 

This one makes sense for systems containing ANY of the requested ice (not quite what I need)

 

SELECT s.solarSystemID
FROM mapRegionIceTypes i
LEFT JOIN invTypes t ON i.typeID = t.typeID
LEFT JOIN mapSolarSystems s ON s.regionID = i.regionID
WHERE  i.maxSecurity >= s.security 
AND i.minSecurity <= s.security 
AND i.typeID IN (16264,16262,16267)

 

How would I select systems with ALL of the requested types of ice?

 

Thanks for your feedback in advance. Let me know if I can provide further info.

[PFMaBiSmAd]

The cleanest way (for an arbitrary number of 'having all x' present) is to use IN() the way you are currently, but GROUP BY s.solarSystemID and then get a count of how many are in each group, and add a HAVING count = 3.

 

Untested but should work -

 

SELECT s.solarSystemID, count(*) as cnt
FROM mapRegionIceTypes i
LEFT JOIN invTypes t ON i.typeID = t.typeID
LEFT JOIN mapSolarSystems s ON s.regionID = i.regionID
WHERE  i.maxSecurity >= s.security
AND i.minSecurity <= s.security
AND i.typeID IN (16264,16262,16267)
GROUP BY s.solarSystemID
HAVING cnt = 3

 

The HAVING cnt = x would need to match the number of different choices being used in the IN() term.

[fenway]

Cleanest, yes; fastest, no.

 

The most efficient way to is to self-join N times -- as long as N is small.

[ultrus]

PFMaBiSmAd,

I was just about to post a similar solution but it was semi-broke without the "HAVING" part:

SELECT s.solarSystemID, COUNT(i.typeID) AS count
FROM mapRegionIceTypes i
LEFT JOIN invTypes t ON i.typeID=t.typeID
LEFT JOIN mapSolarSystems s ON s.regionID=i.regionID
WHERE  i.maxSecurity >= s.security 
AND i.minSecurity <= s.security 
AND i.typeID IN (16266,16267)
GROUP BY s.solarSystemID
ORDER BY count DESC

 

Here's the fixed one based on your advise:

 

SELECT s.solarSystemID, COUNT(i.typeID) AS count
FROM mapRegionIceTypes i
LEFT JOIN invTypes t ON i.typeID=t.typeID
LEFT JOIN mapSolarSystems s ON s.regionID=i.regionID
WHERE  i.maxSecurity >= s.security 
AND i.minSecurity <= s.security 
AND i.typeID IN (16266,16267)
GROUP BY s.solarSystemID
HAVING count = 2

 

Awesome! Thank you super much.

 

Fenway,

Thanks for your feedback as well. Since the database is somewhat small and won't grow much, the above works fine. Otherwise, there could be up to 12 joins. I don't know what the performance difference would be at this time.

 

Best regards,