Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New Coder
    Join Date
    Nov 2007
    Posts
    34
    Thanks
    9
    Thanked 0 Times in 0 Posts

    SELECT based on multiple values

    I having a difficult time trying to do something that I thought would be alot easier. I'm trying to query only one table, return one column's data based on another column's values.

    I bassicaly have:

    Table Name: accounts
    user_id, org_id

    I need to select single user_id's that has the following criteria:

    1 entry WHERE `org_id` = 'LL1' OR `org_id` = 'LL2'
    AND
    4 entires WHERE `org_id` = 'LL3'
    AND
    1 entry WHERE `org_id` = 'LL4'
    AND
    1 entry WHERE `org_id` = 'LL5'
    Last edited by weaksauce; 11-30-2011 at 08:37 PM.
    Time after Time

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    See UNION in the manual.

  • #3
    New Coder
    Join Date
    Nov 2007
    Posts
    34
    Thanks
    9
    Thanked 0 Times in 0 Posts
    while using UNION it seems that the select statements are becoming OR's instead of AND's

    I ran this:
    Code:
    SELECT distinct(`user_id`) FROM `accounts` WHERE `org_id` = 'LL1' OR `org_id` = 'LL2'
    UNION
    SELECT distinct(`user_id`) FROM `accounts` WHERE `org_id` = 'LL4' HAVING COUNT(*) >= 4
    UNION
    SELECT distinct(`user_id`) FROM `accounts` WHERE `org_id` = 'LL5'
    UNION
    SELECT distinct(`user_id`) FROM `accounts` WHERE `org_id` = 'LL6'
    UNION
    SELECT distinct(`user_id`) FROM `accounts` WHERE `org_id` = 'LL8'
    If a user_id met any of the where clauses then it would be returned compared to having to meet all requests
    Last edited by weaksauce; 11-30-2011 at 07:20 PM.
    Time after Time

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Instead of having us guess, how about you post the query and we can assist you from there.

  • #5
    New Coder
    Join Date
    Nov 2007
    Posts
    34
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Code:
    SELECT `user_id` 
    FROM `accounts` 
    WHERE `org_id` IN ('LL1','LL2','LL5','LL6','LL8') 
    GROUP BY `user_id` 
    HAVING COUNT(distinct(`org_id`)) = 4 OR COUNT(distinct(`org_id`)) = 5
    Would possibly work but then I have to do another method test where org_id = LL4 and count >= 4
    Last edited by weaksauce; 11-30-2011 at 07:30 PM.
    Time after Time

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    That sounds like a fun one.
    1 entry WHERE `org_id` = 'LL1' OR `org_id` = 'LL2'
    AND
    4 entires WHERE `org_id` = 'LL3'
    AND
    1 entry WHERE `org_id` = 'LL4'
    AND
    1 entry WHERE `org_id` = 'LL5'
    How about:
    Code:
    SELECT user_id,
           SUM(IF org_id IN ('LL1','LL2'),1,0) AS LL1count,
           SUM(IF org_id='LL3',1,0) AS LL3count,
           SUM(IF org_id='LL4',1,0) AS LL4count,
           SUM(IF org_id='LL5',1,0) AS LL5count
    FROM accounts 
    GROUP BY user_id
    HAVING LL1Count=1 AND LL3Count=4 AND LL4Count=1 AND LL5Count=1
    That will get you the counts in the extra columns of the SELECT, but you can just ignore them in your PHP (or whatever) code.

    If you *must* get back only a single field for some reason then just wrap all that in an outer SELECT:
    Code:
    SELECT X.user_id FROM (
         SELECT user_id,
               SUM(IF org_id IN ('LL1','LL2'),1,0) AS LL1count,
               SUM(IF org_id='LL3',1,0) AS LL3count,
               SUM(IF org_id='LL4',1,0) AS LL4count,
               SUM(IF org_id='LL5',1,0) AS LL5count
        FROM accounts 
        GROUP BY user_id
        HAVING LL1Count=1 AND LL3Count=4 AND LL4Count=1 AND LL5Count=1
        ) AS X
    But that seems unnecessary.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    weaksauce (11-30-2011)

  • #7
    New Coder
    Join Date
    Nov 2007
    Posts
    34
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Solved

    That's awesome... You have a slight syntax error with your SUM/IFS

    Code:
    SUM(IF org_id IN ('LL1','LL2'),1,0)
    should be...

    Code:
    SUM(IF(`org_id` IN ('LL1','LL2'),1,0))
    but other than that the code works great
    Last edited by weaksauce; 11-30-2011 at 08:36 PM.
    Time after Time

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,919
    Thanks
    79
    Thanked 4,423 Times in 4,388 Posts
    Whoops...teach me to try to type and drink coffee at the same time.

    COMMENT: You truly don't need the back ticks around org_id or user_id or accounts. You only need them when you use a name that is a keyword in MySQL or when a name uses characters outside the normal set of name characters.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Sorry yes I misunderstood your requirements.

    In future, it would help if you use an actual example with some sample rows and expected output, when you ask for assistance, that way it is more clear.


  •  

    Posting Permissions

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