I need something like IN(1,2,3) but more like IN(1 AND 2 AND 3)??

[ultrus]

Maybe I'm just having a bad night, so please slap me in the face if needed.

 

I have a table like this:

 

Table: system_contents
+-----+--------+--------------+
| id  | name   | contains     |
+-----+--------+--------------+
| 1   | Sys1   | asteroids    |
| 2   | Sys2   | asteroids    |
| 3   | Sys2   | planets      |
+-----+--------+--------------+

 

I want to select systems that contain asteroids AND planets. The following query will NOT work for that because it selects systems with asteroids OR planets (right?):

 

SELECT name FROM system_contents WHERE contains IN('asteroids','planets') GROUP BY name

 

Any thoughts on how to adjust? Thanks much in advance!

[PFMaBiSmAd]

Assuming you don't have duplicates (i.e. sys2, asteroids, sys2, asteroids), this should (untested) work -

SELECT name, count(*) as cnt FROM system_contents WHERE contains IN('asteroids','planets') GROUP BY name HAVING cnt = 2

[ultrus]

PFMaBiSmAd,

Nice! I didn't know about 'HAVING'.

 

However... that table I showed is a bit simplified. Sometimes there are multiple planets with different planet names in the same system (honestly, asteroid doesn't fit, it's actually different planet types) 

 

I like the count addition. If getting it to work properly, it would be nice to know how many total matched entities there are per system. I'll do some more research, but any additional feedback again is much appreciated.

[ultrus]

Does something like this make more sense? I could probably generate something like this dynamically:

 

SELECT c1.name, count(*) as cnt 
FROM system_contents c1
LEFT JOIN system_contents c2 ON c1.id = c2.id
WHERE c1.contains = 'asteroids'
AND c2.contains = 'planets' 
GROUP BY name

 

Now that I type that, it doesn't make sense. Perhaps some sleep will help.

 

EDIT: Another rendition:

 

SELECT c1.name, count(*) as cnt 
FROM system_contents c1
LEFT JOIN system_contents c2 ON c1.name = c2.name
WHERE c1.contains = 'asteroids'
AND c2.contains = 'planets'
AND c1.contains != c2.contains 
GROUP BY name

[bluejay002]

Am not sure if I am getting what you want but you may try this if you want:

 

SELECT name
FROM system_contents 
WHERE contains LIKE '%asteroids%'
AND contains LIKE '%planets%'
GROUP BY name

 

Though am not really getting if this is what you want to achieve though.

 

bluejay,

[fenway]

ultrus has the right idea.

[ultrus]

I'm still digging, and will post a solution as early as this morning.

[ultrus]

OK. I got it figured out. Instead of going crazy, joining a non-normalized table with itself many, many times, I just created a new table containing all the information I will need. The final query that creates the table in its full context is now available to the general public here:

 

http://wiki.eve-id.net/Create_Table_with_Planet_Type_Counts_in_Each_System