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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
