query, count, group issues

[NetNerd85]

Hi, I have a search query where I'm looking for all People where they have the chosen UserAttributes. Each UserAttribute is part of a Group, when searching the the relationship between the UserAttribute Groups is AND and the Attribute value is OR. An example would be, you search for People in the Attribute Group "sex", selecting "male" OR "female" AND in another Attribute Group "hair colour", selecting "blonde" OR "brown" OR "black".

The following query works for selecting People that have selected UserAttributes that are in the UserAttributes Groups.

SELECT DISTINCT `Person`.`ID`, `Person`.`Firstname`, `Person`.`Surname`, COUNT(*), GROUP_CONCAT(CAST(`UserAttribute`.`UserAttributeGroupID` AS char)) as uagc 
FROM Person, Person_UserAttributes, UserAttribute
WHERE (`Person`.`ID` = `Person_UserAttributes`.`PersonID` 
AND `UserAttribute`.`ID` = `Person_UserAttributes`.`UserAttributeID` 
AND `Person_UserAttributes`.`UserAttributeID` IN (41,42,43,11,12,51,75,15,16,17,9,10) 
) 
GROUP BY `Person`.`ID` HAVING ( COUNT(`UserAttribute`.`UserAttributeGroupID`) = 4 )

The UserAttribute Group count is 4, that number is from the code because you can tell from the POST Array (PHP) that the group number is 4. This will of course change with the amount of different UserAttributes selected from different UserAttribute Groups. I can see from the result the count is 4, the GROUP_CONCAT shows the UserAttribute Group ids.

The problem is when I want to search not only for the User Attributes but for other criteria such as Hobbies. The following shows no results.

SELECT DISTINCT `Person`.`ID`, `Person`.`Firstname`, `Person`.`Surname`, COUNT(*), GROUP_CONCAT(CAST(`UserAttribute`.`UserAttributeGroupID` AS char)) as uagc 
FROM Person, Person_UserAttributes, UserAttribute, Hobbies 
WHERE (`Person`.`ID` = `Person_UserAttributes`.`PersonID` 
AND `UserAttribute`.`ID` = `Person_UserAttributes`.`UserAttributeID` 
AND `Person_UserAttributes`.`UserAttributeID` IN (41,42,43,11,12,51,75,15,16,17,9,10) 
AND `Person`.`ID` = `Hobbies`.`PersonID` AND `Hobbies`.`HobbyCategoryID` IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118)
) 
GROUP BY `Person`.`ID` HAVING ( COUNT(`UserAttribute`.`UserAttributeGroupID`) = 4 )

I can only get results from the new query if I change COUNT(`UserAttribute`.`UserAttributeGroupID`) = 4 to this COUNT(`UserAttribute`.`UserAttributeGroupID`) >= 4, and the count takes into account the Hobby Categories.

I know my approach must be wrong but I have no idea what to do next , please help

[r937]

AND `Person_UserAttributes`.`UserAttributeID` IN (41,42,43,11,12,51,75,15,16,17,9,10)

The UserAttribute Group count is 4, that number is from the code because you can tell from the POST Array (PHP) that the group number is 4.

uh, no, i cannot

looks to me like the userattribute count is 12

you will need to explain in more detail how you came up with 4



p.s. you can replace this --

Hobbies.HobbyCategoryID IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118)

with this --

Hobbies.HobbyCategoryID BETWEEN 1 AND 118

[NetNerd85]

uh, no, i cannot
looks to me like the userattribute count is 12
you will need to explain in more detail how you came up with 4

AND `Person_UserAttributes`.`UserAttributeID` IN (41,42,43,11,12,51,75,15,16,17,9,10)

The UserAttribute Group count is 4, that number is from the code because you can tell from the POST Array (PHP) that the group number is 4.

You see the UserAttribute count is 12, the UserAttribute Group count that is found in the PHP code is 4. Group, not the mysql GROUP, a group, like a group of people watching tv.

I fixed the problem any way using a subquery:

SELECT DISTINCT `Person`.`ID`, `Person`.`Firstname`, `Person`.`Surname`, COUNT(*), GROUP_CONCAT(CAST(`UserAttribute`.`UserAttributeGroupID` AS char)) as uagc 

FROM Person, Person_UserAttributes, UserAttribute 

WHERE (`Person`.`ID` = `Person_UserAttributes`.`PersonID` 
AND `UserAttribute`.`ID` = `Person_UserAttributes`.`UserAttributeID` 
AND `Person_UserAttributes`.`UserAttributeID` IN (41,42,43,11,12,51,75,15,16,17,9,10) 
AND `Person`.`ID` IN (SELECT `PersonID` FROM Hobbies WHERE `Hobbies`.`HobbyCategoryID` IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247))  
	
) 
GROUP BY `Person`.`ID` HAVING ( COUNT(`UserAttribute`.`UserAttributeGroupID`) = 4 )

"here's your sign"