SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict NetNerd85's Avatar
    Join Date
    Aug 2005
    Location
    Australia
    Posts
    298
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question query, count, group issues

    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.

    Code:
    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.

    Code:
    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
    a new day, a new beginning
    never follow the crowd, the crowd is poor!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by NetNerd85 View Post
    Code:
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict NetNerd85's Avatar
    Join Date
    Aug 2005
    Location
    Australia
    Posts
    298
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    Code:
    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:

    Code:
    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"
    a new day, a new beginning
    never follow the crowd, the crowd is poor!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •