[EMAIL PROTECTED] wrote on 01/11/2006 02:42:27 PM:
> 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
>
One way to do this is to run a query that counts how many tags that belong
to a picture match your list of criteria. Run this and you will see how
many pictures have either or both of the tags you are looking for:
SELECT p.id, count(t.tagid) matches
FROM pics p
LEFT JOIN tags t
ON p.picid = t.picid
AND t.tag in ('red','car')
GROUP by p.id;
There are three ways to declare which rows of data participate in a query:
The ON clauses of your JOINs, the WHERE clause, and the HAVING clause.
JOINs are evaluated first and define what you want the query to look at as
its source data. The WHERE clause refines the rows identified by the JOINs
so that you continue evaluation with only those rows you want. The GROUP
BY is the next part of the query to be evaluated next. That means you
can't WHERE the results of a GROUP BY. The GROUP BY is where you count how
many matches you had so we need to use the third option, the HAVING
clause, to limit your results to just those pics with 2 matches against
your 2 search terms. Against your simplified tables, the query looks like
this:
SELECT p.id, count(t.tagid) matches
FROM pics p
LEFT JOIN tags t
ON p.picid = t.picid
AND t.tag in ('red','car')
GROUP by p.id
HAVING matches=2;
There are other ways to solve this type of query problem but they suffer
from the inability to scale easily to multiple terms. If you need to match
2 of 3 terms, just put all three terms in the ON clause and leave the
HAVING clause to look for just 2 matches. This pattern is very flexible
and simple to learn.
Hope this helped,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
