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:li...@l33t-d00d.co.uk]
> Sent: Monday, April 29, 2013 10:43 AM
> To: mysql@lists.mysql.com
> 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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
