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
means
WHERE (duespaid AND cat1) OR cat2
That is probably not what you wanted -- add parens like
WHERE duespaid AND (cat1 OR cat2 ...)

But...

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: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql

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