Can a JOIN do this?

Databases
1

I have a query

SELECT 
user_id,name,email
FROM users

which shows all the users.
I have another

SELECT 
COUNT(device_id) AS count,created_by
FROM devices
GROUP BY created_by

which shows the number of devices created by each user.
Is there a way to combine these 2 queries into 1?

2

what’s the link? Is created_by = user_id?

3

oh my bad, the users table record, email
is is supposed to be the same as the created_by record in the devices table

I tried to add

INNER Join devices ON users.email = devices.created_by

But it didnt work

4

Immediate response: Then what’s the point of user_id, if both name and email are unique values. and you’re going to replicate email in the devices table?

Assuming every device has a created_by entry, then what you are describing is a basic Inner Join. The group by makes it a little more troublesome; either you’d have to subquery the aggregation query, or apply aggregation functions to the user_id and name (Hint: The MAX of 15 copies of “John” is still just “John”.)

5

ok, so instead of created_by being an email, should I just make it a user_id (in the devices table)
Also, I tried a INNER JOIN

SELECT user_id,users.name,email,
COUNT(device_id) AS count, devices.created_by
FROM users
INNER JOIN devices ON users.email = devices.created_by

and

image
image1920×1080 199 KB

I tried a subquery

SELECT user_id,name,email,
(
    SELECT COUNT(device_id) AS count,created_by
	FROM devices
	GROUP BY created_by
) 
FROM users

But

image
image1920×1080 198 KB

I’m confused to your last suggestion (Isn’t MAX used to find the largest number) and how can that be used to ccount the number of times John is used?

6

guess my syntax was screwed up

SELECT user_id,name,email
FROM users
WHERE email IN (
    SELECT created_by
	FROM devices
)

image
image1920×1080 147 KB

but how do I get the number of devices those users added?

7

you forgot the grouping.

Your subquery’s in the wrong place, it would have to be a joined subquery:

SELECT u.user, u.name, u.email, c.count 
FROM   users u
JOIN   (subquery here) c
ON     (c.created_by = u.whicheverfieldyouused)

When you try to apply the grouping to the first example, you’ll see why. MySQL is going to complain at you that you can’t request non-aggregated columns in a grouped query if they aren’t part of the grouping index.

1 Like
8

Yes, the created_by column values should be the user_id, for at least these three reasons -

  1. It will use the least amount of storage space.
  2. It will result in the fastest queries.
  3. It will allow the email address to be edited, without requiring you to update all the data related to a user.

In the first post in this thread, in the second query, do you understand what the SELECT COUNT(expr) and the GROUP BY column(s) terms do? GROUP BY … consolidates rows having the same column(s) values together. COUNT(expr) counts the number of non-null values for expr in each group. If you use COUNT() without a GROUP BY, you get the count value for all the rows that the query matched.

So, starting with the first query, where you are selecting some user columns FROM the users table, just add the things you are selecting from the JOINed query, add the JOIN table and the ON … condition, and add the GROUP BY term so that the COUNT() term will have something per group to count.

Note: If you use table alias names, such as u and d for this example query, then always use alias.column syntax, even in the cases where they are not required, it will be clearer to you, and anyone reading your query, what you are trying to accomplish.

Note: almost every SELECT query should have an ORDER BY term to get the data in the order that you want it. When you use a GROUP BY, this causes an ‘internal’ ORDER BY the columns in the GROUP BY. If you want the data in a different order than produced by that, you need to add an ORDER BY term.

The first query after adding the things you are selecting from the JOINed query and adding table alias names -

SELECT
 u.user_id, u.name, u.email, COUNT(d.device_id) AS count
FROM users u

Note: selecting created_by at this point is redundant since it will be the same as the user_id (email in your current design.)

The above query after adding the JOIN table and ON … condition -

SELECT
 u.user_id, u.name, u.email, COUNT(d.device_id) AS count
FROM users u
INNER JOIN devices d ON u.user_id = d.created_by

The above query after adding the GROUP BY term, so that the COUNT() term will have something to count per group -

SELECT
 u.user_id, u.name, u.email, COUNT(d.device_id) AS count
FROM users u
INNER JOIN devices d ON u.user_id = d.created_by
GROUP BY u.user_id

Note: you should group by something in the ‘parent’ table in a query, since there may not be any ‘child’ values for all types of join queries.

1 Like
9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.