CODEWITHSUNDEEP
mysqlsqlpostgresql
SezarA
SezarA

Reputation: 35

SQL query from many to many relation

I have many to many relation

  table:  images    
  id  imageName  
   1    pic01
   2    pic02
   3    pic03    

  table:  imagesKeywords
  imageId  keywordId
   1        2
   1        3
   1        4
   2        3
   3        1
   3        4
   3        2

  table:  keywords
  id  keywordName  
   1    car
   2    tree
   3    cat
   4    phone

Each image has some keywords, and different images can have the same keyword.

I need to make a search for images , they have a specific keywordName's.

example-1: search for car and phone

the result should be : pic03

example-2: search for tree and phone

the result should be : pic01, pic03

Upvotes: 0

Views: 92

Answers (3)

krithikaGopalakrishnan
krithikaGopalakrishnan

Reputation: 1335

One possible solution,

with subquery as
       (select i1.imageName, k1.keywordName from keywords k1 join imagekeywords ik1 on k1.id=ik1.keywordId join images i1 on i1.id = ik1.imageId )    
 select a.imageName from subquery a join subquery b on a.imageName=b.imageName where a.keywordName ='car' and b.keywordName='phone';

Upvotes: 0

Frosted Developer
Frosted Developer

Reputation: 129

As I understand you, this should work:

select i.imageName as name from keywords k
join imagesKeywords iK on k.id = iK.keywordId
join images i on iK.imageId = i.id
group by i.imageName;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You appear to want JOIN with GROUP BY Clause :

select i.imageName
from images i inner join
     imagesKeywords ik 
     on ik.imageId = i.id inner join 
     keywords k
     on k.id = ik.keywordId 
where k.keywordName in ('car', 'phone')
group by i.imageName
having count(*) = 2;

Upvotes: 3

Related Questions