List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 11 2006 8:25pm
Subject:Re: Help crafting an SELECT
View as plain text  
sydneymartin@stripped 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


Thread
Help crafting an SELECTMike Martin11 Jan
  • Re: Help crafting an SELECTSGreen11 Jan
  • Re: Help crafting an SELECTPeter Brawley11 Jan
Re: Help crafting an SELECTMike Martin11 Jan
Re: Help crafting an SELECTSGreen11 Jan