0

I have a table with three columns.

image           ppid    cpid
1st_image.jpg   10395   10019
1st_image.jpg   10395   10371
1st_image.jpg   10395   10311

2nd_image.jpg   10395   10019
2nd_image.jpg   10395   10371

How can I select the 1st_image.jpg where the ppid is 10395 and the cpid is 10019, 10371 and 10311? Basically, I want to only select the image if all cpids match but because they are spread over several rows, I cannot select them with a simple WHERE statement.

Edit: I forgot to explicitly mention that 2nd_image.jpg should not be selected because it does not match all cpid's (10019, 10371, 10311)

Frank Vilea
  • 8,323
  • 20
  • 65
  • 86
  • possible duplicate of [MySQL Select ID's which occur on different rows with multiple specific values for a column](http://stackoverflow.com/questions/7407001/mysql-select-ids-which-occur-on-different-rows-with-multiple-specific-values-fo) – Bill Karwin Apr 21 '14 at 18:40
  • Thank you Bill, I've tried your second solution, but it does not seem to work. I receive a count of 5, but I assume, the count should be 3. – Frank Vilea Apr 21 '14 at 19:07
  • You can probably find other answers to questions with the [tag:relational-division] tag that I added. – Bill Karwin Apr 21 '14 at 19:37

2 Answers2

0

select image from mytable where ppid=10395 and cpid in (10019, 10371 , 10311)

atxdba
  • 5,158
  • 5
  • 24
  • 30
  • Thanks for your reply. This was also my first idea. But it returns all images (1st_image.jpg and 2nd_image.jpg) when I'm only looking for the first one. – Frank Vilea Apr 21 '14 at 18:57
0

I am not 100% sure what you want but I think this will work

select 
  image, 
  group_concat(cpid) 
from 
  (select '1st_image.jpg' image,   10395 ppid,   10019 cpid union all
  select '1st_image.jpg' image,   10395 ppid,   10371 cpid union all
  select '1st_image.jpg' image,   10395 ppid,   10311 cpid union all
  select '2nd_image.jpg' image,   10395 ppid,   10019 cpid union all
  select '2nd_image.jpg' image,   10395 ppid,   10371 cpid) t1
group by 
  image
having
  group_concat(cpid) like '%10371%' and
  group_concat(cpid) like '%10311%' and
  group_concat(cpid) like '%10019%'

You can replace t1 with your table. Essentially what is happening is a group on image and then concatenating all cpids into a single row. You can add any filters regarding ppid if you want.

Ryan-Neal Mes
  • 6,003
  • 7
  • 52
  • 77
  • I've tried this solution and it seems to work. But wouldn't it be a performance hog if the table gets bigger? – Frank Vilea Apr 21 '14 at 19:13
  • Potentially, but the way your data is stored you don't have too many options. You could pivot the table so you have select image, ppid, cpid_10019, cpid_10371, cpid_10311 - then you could use a where statement. Doing it this way isn't really that flexible, but it would achieve the same thing. Essentially you would use case statements to get the pivot and group by to merge them all into one row. Personally I would go with this just since it's a lot less complex @user1288263 – Ryan-Neal Mes Apr 22 '14 at 07:37