List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 30 2013 11:13pm
Subject:RE: Rookie question
View as plain text  
OR would not show dups.

WHERE duespaid AND cat1 OR cat2
WHERE (duespaid AND cat1) OR cat2
That is probably not what you wanted -- add parens like
WHERE duespaid AND (cat1 OR cat2 ...)


That is not a good way to build a schema.  What will happen when you add category9?

Plan A:  Have another table that says which categories a user has.  There would be 0-8
rows in this new table for each category.
    SELECT d.* FROM directory d JOIN categories c ON d.userid = c.userid
        WHERE c.category IN (1,2,3,4,5,6,7,8);

Plan B:  Use a SET as a single column for all the categories.  Then
    AND (categories & x'ff') != x'00'
would check that at least one bit is on in the bottom 8 bits of that SET.  (TINYINT
UNSIGNED would work identically.  Change to SMALLINT UNSIGNED for 9-16 categories; etc.)

There is probably a Plan C.

> -----Original Message-----
> From: Gary Smith [mailto:lists@stripped]
> Sent: Monday, April 29, 2013 10:43 AM
> To: mysql@stripped
> Subject: Re: Rookie question
> On 29/04/2013 18:29, Patrice Olivier-Wilson wrote:
> > Hi all:
> >
> > I have a membership directory where folks can belong to more than one
> category. But all folks do not qualify for a category. So I want to
> list folks who have qualified in a category but not have them repeat.
> So if member 1 is in cat 3 and cat 5, I want their name only to show up
> once. Here's what I have so far, but it shows a member listed more than
> once.
> select distinct ?
> Gary
> --
> MySQL General Mailing List
> For list archives:
> To unsubscribe:

Rookie questionPatrice Olivier-Wilson29 Apr
  • Re: Rookie questionGary Smith29 Apr
    • RE: Rookie questionRick James30 Apr