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
> select distinct ?
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql