Search for multiple entries in the same field 1

[MrBelfry]

Hi there

I've written a little photo gallery to manage all our church photos (which are numerous!). It is possible to tag the photos with certain keywords ie. BBQ, youth etc the table looks like this

id = id of photograph
tag = tag keyword

I'm able to search for photographs with multiple tags so for instance I can get all photos tagged with youth or 2009 or november. However what I would also like to be able to do is search for photo's that are tagged with ALL the keywords ie. photos that are tagged youth AND 2009 AND november. Unfortunately I have ran out of inspiration ;( and I need some assistance

Hope you can help point me in the right direction

Thanks

Richard Clare

 

[r937]

SELECT p.id
     , p.imagesize
     , p.datetaken
  FROM ( SELECT id
           FROM phototags
          WHERE tag IN ( 'youth','2009','november' )
         GROUP
             BY id
         HAVING COUNT(*) = 3 ) AS d
INNER
  JOIN photographs AS p
    ON p.id = d.id

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[MrBelfry]

Thanks that works perfectly. I was trying to work a solution in php that did a similar thing but seemed terribly inefficient because i was getting a whole bunch of records from the database that i didn't need. I knew there must have been a sql solution and this is great