[EMAIL PROTECTED] wrote on 01/11/2006 03:53:26 PM:
> On 1/11/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> >
> > [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
> >
> >
>
> 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
