...

View Full Version : Resolved SELECT based on multiple values



weaksauce
11-30-2011, 05:57 PM
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'

guelphdad
11-30-2011, 06:05 PM
See UNION in the manual.

weaksauce
11-30-2011, 06:22 PM
while using UNION it seems that the select statements are becoming OR's instead of AND's

I ran this:


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

guelphdad
11-30-2011, 06:58 PM
Instead of having us guess, how about you post the query and we can assist you from there.

weaksauce
11-30-2011, 07:06 PM
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

Old Pedant
11-30-2011, 08:16 PM
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:


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:


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.

weaksauce
11-30-2011, 08:34 PM
That's awesome... You have a slight syntax error with your SUM/IFS


SUM(IF org_id IN ('LL1','LL2'),1,0)

should be...


SUM(IF(`org_id` IN ('LL1','LL2'),1,0)) but other than that the code works great

Old Pedant
11-30-2011, 09:06 PM
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.

guelphdad
11-30-2011, 09:11 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum