Resolved single table left join?

[weaksauce]

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]

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]

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]

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]

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]

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]

??? 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]

Perfect. Thanks!