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 8 of 8
  1. #1
    New Coder
    Join Date
    Nov 2007
    Posts
    34
    Thanks
    9
    Thanked 0 Times in 0 Posts

    single table left join?

    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 :
    Code:
    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:
    Code:
    user_id     count
    123          2
    456          0
    789          2
    The only solutions I could come up with was creating a temporary table...
    Code:
    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.
    Code:
    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!
    Last edited by weaksauce; 01-12-2013 at 12:12 AM.
    Time after Time

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    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
    Code:
    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:
    Code:
    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.
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    In other words, if you had shown us
    Code:
    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
    Code:
    user_id     count
    123          2
    456          1
    789          2
    then I'd know I was right.
    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.

  • #4
    New Coder
    Join Date
    Nov 2007
    Posts
    34
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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.
    Last edited by weaksauce; 01-11-2013 at 10:03 PM.
    Time after Time

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    OH!!!

    COMPLETELY different that what I thought!


    Yes, LEFT JOIN.

    Code:
    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.
    Last edited by Old Pedant; 01-11-2013 at 10:32 PM.
    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 (01-11-2013)

  • #6
    New Coder
    Join Date
    Nov 2007
    Posts
    34
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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?

    Code:
    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'...)
    Time after Time

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,021
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    ??? 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
    Code:
    WHERE X.user_id IN ('USER1','USER2'...)
    ????

    But if you do need it, then there's an easier way:
    Code:
    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.
    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.

  • #8
    New Coder
    Join Date
    Nov 2007
    Posts
    34
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Perfect. Thanks!
    Time after Time


  •  

    Tags for this Thread

    Posting Permissions

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