Mike,
You're in the right place. If I understand your requirement correctly,
you want entries which...
match on id,
match a selection from ('Mustang' or 'Beetle'),
match multiple values in tags (eg 'red' and 'car').
Here's one solution:
SELECT p.photo, t.tag
FROM pics p
INNER JOIN tags c USING (picid)
WHERE p.photo IN('Mustang','Beetle')
AND t.tag IN('car','red');
Here's a slightly different-looking version of the same answer:
SELECT
p.photo,
GROUP_CONCAT(t.tag)
FROM pics p
INNER JOIN tags c USING (picid)
WHERE p.photo IN('Mustang','Beetle')
AND t.tag IN('car','red')
GROUP BY p.photo;
PB
-----
Mike Martin wrote:
>Hi List!
>
>Please forgive me if I'm asking the wrong list (and please guide me to
>the right place).
>
>I have two tables (simplified for this example) "pics" and "tags" like so:
>
>pics:
>picid photo
>1 Mustang
>2 Apple
>3 Rock
>4 Beetle
>
>tags:
>tagid picid tag
>1 1 Car
>2 1 Red
>3 2 Red
>4 3 Quartz
>5 4 Car
>6 2 Food
>7 1 1979
>
>I'm trying to craft a query which will join the tables by the picid
>column and return the entries which match multiple "tags". For
>instance I want to query for items that are both "red" and "car" (to
>get the records: Mustang and Beetle in this case).
>
>I assume I should start with something like:
>
>SELECT * FROM pics JOIN tags ON pics.picid=tags.tagid WHERE .....
>
>and that's where I get stuck. tag="red" AND tag="car" isn't right and
>neighter is tag="red" OR tag="car".
>
>I think this should be simple ... but I'm too new to SQL. I've messed
>around with GROUPs, UNIONs, etc. but I'm just out of my league. Can
>someone help, or point me to a good tutorial/explanation that would
>help?
>
>Thanks!
>
>MikeMartin
>
>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.17/226 - Release Date: 1/10/2006
