1

I'm struggling for hours with something that seems so easy but I cant make it to work or find something similar in Google.

I have 2 tables 

images

and 

tags

They have a relation of Many to Many so I have another pivot table named image_tag

What Im trying to achive is select all images that has tag_id=4 and tag_id=1

My first attemp was something like this:

SELECT * from images as a INNER JOIN
image_tag as b on a.id=b.image_id 
WHERE b.tag_id=4 and b.tag_id=1

Of couse this gave me 0 result as you cant use AND condition directly to pivot.

After that I tried this:

SELECT * FROM images as a
INNER JOIN image_tag as b on a.id=b.image_id
WHERE b.tag_id IN (1,4)

This returns all the images that either has tag_id=1 or tag_id=4 tried also Inner joining the same pivot table but cant make it to work no matter what

EDIT: Adding the sql fiddle. http://sqlfiddle.com/#!9/1726b0/1 the result should be images with ids 4,5,6,7

IseNgaRt
  • 601
  • 1
  • 4
  • 22
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry Apr 27 '18 at 22:37

2 Answers2

3

Use group by and having to get all image_id's meeting the criteria and use the resulting id's for join.

SELECT a.* 
FROM images as a
INNER JOIN (select image_id 
            from image_tag 
            where tag_id IN (1,4) 
            group by image_id
            having count(distinct tag_id)=2
           ) b on a.id=b.image_id
Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • Isnt there any way to "dodge" the count(distinct tag_id)=* as I will need to calculate it on every query ? – IseNgaRt Apr 27 '18 at 22:33
0

You can do this with just the table image_tag. You have to join it with itself in order to get all the combinations. This way you can then select the rows witch will have both tags.

SELECT a.image_id 
FROM image_tag as a 
inner join image_tag as b on a.image_id=b.image_id 
where a.tag_id=4 and b.tag_id=1
Antony Roussos
  • 83
  • 6