...

View Full Version : Resolved single table left join?



weaksauce
01-11-2013, 08:44 PM
I have a single table that I feel is giving me more problems than it should. I want to select a set of users and a set of organizations that I want to check and return a count that also includes null values.

only_table :

user_id org_id
123 ABC
456 AAA
789 DEF
123 ABC
789 GHI
456 BBB
907 DEF
878 ABC

I would want to select only Users: 123,456,789 and Orgs: ABC,DEF,GHI

Example Return:

user_id count
123 2
456 0
789 2

The only solutions I could come up with was creating a temporary table...

CREATE TEMPORARY TABLE `temp1` AS (
SELECT `user_id` AS `user`,COUNT(*) AS `num` FROM `only_table`
WHERE `org_id`
IN (
'ABC','DEF','GHI'
)
GROUP BY `user_id`
);
CREATE TEMPORARY TABLE `temp2` AS (
SELECT DISTINCT(`user_id`) AS `user` FROM `only_table`
WHERE `user_id`
IN (
'123','456','789'
)
);
SELECT
`temp2`.`user`,
`temp1`.`num`
FROM `temp2`
LEFT JOIN `temp1` ON
`temp1`.`user` = `temp2`.`user`
GROUP BY `temp2`.`user`;

or being unable to get the null values.

SELECT `user_id`,COUNT(`user_id`) FROM (
SELECT
`only_table`.`user_id`
FROM `only_table`
WHERE
`only_table`.`user_id` IN (
'123',
'456',
'789'
) AND `only_table`.`org_id` IN (
'ABC',
'DEF',
'GHI'
)
) as `temp`
GROUP BY `user_id`

I am being dumb right now or is there some way to retrieve null values without a left join or using some type of temporary table,view,procedure?

Any help will be appreciated, thanks in advance!

Old Pedant
01-11-2013, 09:22 PM
HUH???

I *assume* you mean you want to include NULL values for the user_ids, only?
It would make no sense to accept NULLs for the org_id if you want to get the counts *BY* org_id.

So what's wrong with simply


SELECT org_id, COUNT(*) as howMany
FROM only_table
WHERE (user_id IS NULL OR user_id IN (123,456,789) )
AND org_id IN ('ABC','DEF','GHI')
GROUP BY org_id

If your user_id values are always 0 or greater, another sneaky trick would be:


SELECT org_id, COUNT(*) as howMany
FROM only_table
WHERE IFNULL(user_id,-1) IN (-1,123,456,789)
AND org_id IN ('ABC','DEF','GHI')
GROUP BY org_id


Would have been helpful if you'd shown an example that included NULL values and then the expected results therefrom.

Old Pedant
01-11-2013, 09:24 PM
In other words, if you had shown us

user_id org_id
123 ABC
456 AAA
789 DEF
123 ABC
789 GHI
456 BBB
907 DEF
878 ABC
456 [null]

with results of

user_id count
123 2
456 1
789 2

then I'd know I was right.

weaksauce
01-11-2013, 09:53 PM
user_id and org_id are never null (nor unique), what I'm trying to do is basically take a table and:

select a list of users that are members of a list of orgs and count how many times they are in those specific organizations, there also are other users and orgs outside of my query that I don't care about

Specifically I'm running 64 users and 32 organizations but only 44 users show up because the other 20 don't belong to any of the organizations I'm checking for, I would like the other 20 to show up with a 0 beside them or null for that matter.

Edit: Both solutions above work, just the second sub-query doesn't give null values. I just didn't know to get the null values without using a temporary table.

Old Pedant
01-11-2013, 10:24 PM
OH!!!

COMPLETELY different that what I thought!


Yes, LEFT JOIN.



SELECT D.user_id, COUNT(T.org_id) AS howMany
FROM ( SELECT DISTINCT user_id FROM only_table ) AS D
LEFT JOIN only_table AS T
ON (
D.user_id = T.user_id
AND T.org_id IN ( 'ABC','DEF','GHI' )
)
GROUP BY D.user_id
ORDER BY D.user_id

("D" for "Derived", "T" for "Table")

Note that the T.org_id restriction *MUST* be part of the ON condition. It can *NOT* be moved to the WHERE.

The COUNT(T.org_id) can of course be COUNT(T.user_id) instead. Makes no difference.

weaksauce
01-11-2013, 11:05 PM
That worked... You're awesome!

I had to do a sub-query to only return the specific user list, I think I could have added it to the JOIN but it would have taken longer right?


SELECT * FROM (
SELECT D.user_id, COUNT(T.org_id) AS howMany
FROM ( SELECT DISTINCT user_id FROM only_table ) AS D
LEFT JOIN only_table AS T
ON (
D.user_id = T.user_id
AND T.org_id IN ('ORG1','ORG2'...)
)
GROUP BY D.user_id
ORDER BY D.user_id
) AS X
WHERE
X.user_id IN ('USER1','USER2'...)

Old Pedant
01-11-2013, 11:57 PM
??? I thought you wanted *ALL* users? You wrote

I'm running 64 users and 32 organizations but only 44 users show up because the other 20 don't belong to any of the organizations I'm checking for, I would like the other 20 to show up with a 0 beside them
I don't see anything in that statement that says you need to limit the users.

So why do you need the

WHERE X.user_id IN ('USER1','USER2'...)
????

But if you do need it, then there's an easier way:


SELECT D.user_id, COUNT(T.org_id) AS howMany
FROM (
SELECT DISTINCT user_id FROM only_table
WHERE user_id IN ('USER1','USER2',...)
) AS D
LEFT JOIN only_table AS T
ON (
D.user_id = T.user_id
AND T.org_id IN ( 'ABC','DEF','GHI' )
)
GROUP BY D.user_id
ORDER BY D.user_id

Why get and count them only to throw them away *after* the count? Just eliminate the unwanted users before the join and count ever take place.

weaksauce
01-12-2013, 12:10 AM
Perfect. Thanks!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum