From: Peter Brawley
Date: January 11 2006 8:29pm
Subject: Re: Help crafting an SELECT
List-Archive: http://lists.mysql.com/mysql/193785
Message-Id: <43C56A9A.209@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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