List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 11 2006 9:53pm
Subject:Re: Help crafting an SELECT
View as plain text  
sydneymartin@stripped wrote on 01/11/2006 03:53:26 PM:

> On 1/11/06, SGreen@stripped <SGreen@stripped> wrote:
> >
> >
> > 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
> >
> >
> 
> Shawn,
> 
> Thanks for your (very fast!) response. Thanks also for you explanation.
> 
> I think there's a problem with using the count though. Will it not
> count ALL the tags, not just the ones that matched the "IN"? Will it
> not include "Apple" in the response since "red" is in the tag list for
> "Apple" and there are "2" tags ("red" and "food"). Similarly will it
> not exclude "Mustang" since it will count 3 tags ("red", "car",
> "1979")?
> 
> I want to ask for "red" and "car" and get back: Mustang, Beetle.
> 
> WAIT!!! I forgot an entry in the data -- the Beetle is red too!
> 
> Here's the data again with the fix:
> 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
> 8        4         Red
> 
> Thanks for the help!

Look more closely at WHAT is being counted.... The first query will show 
you how many tags (of the particular type(s) you are looking for) exist 
for each and every item. There will be values of 0, 1, or 2 in the matches 
column. If you check out which items only have 2 matches you will see that 
they are the items you are trying to identify. The second query picks out 
just those with 2 matches. Is it not working?

Now, this technique will have a problem if you ever allow a single pic to 
have more than one copy of a particular tag. Say for instance that Apple 
had two 'red' tags, it would count both of them and it would be a false 
positive. Good data produces good results.

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