Advertisement

Left join without using view

left-join mysql sql

I have a table structure similar to this:

users

| id |  name  |
|----|--------|
|  1 |  jane  |
|  2 |  john  |

client

| id |    name    | user_id |
|----|------------|---------|
|  1 |  client  1 |    2    |
|  2 |  client  2 |    2    |
|  3 |  client  3 |    2    |
|  4 |  client  4 |    1    |
|  5 |  client  5 |    1    |

products

| id |    name    | user_id |
|----|------------|---------|
|  1 |  product 1 |    1    |
|  2 |  product 2 |    1    |
|  3 |  product 3 |    1    |
|  4 |  product 4 |    2    |
|  5 |  product 5 |    2    |

and I also created two views

client_total

SELECT user_id, COUNT(id) AS client_total FROM client GROUP BY user_id

| user_id | client_total |
|---------|--------------|
|  1      |       2      |
|  2      |       3      |

products_total

SELECT user_id, COUNT(id) AS products_total FROM products GROUP BY user_id

| user_id |products_total|
|---------|--------------|
|  1      |       3      |
|  2      |       2      |

and that was a result

SELECT
    users.*,
    client_total.client_total,
    products_total.products_total
FROM
    users
LEFT JOIN client_total   ON users.user_id = client_total.user_id
LEFT JOIN products_total ON users.user_id = products_total.user_id

| id |  name  |products_total| client_total |
|----|--------|--------------|--------------|
|  1 |  jane  |       3      |       2      |
|  2 |  john  |       2      |       3      |

My question is:

Can I get this same result without using these two views?

Advertisement

Answer

GROUP BY in derived tables (the subqueries), before you LEFT JOIN:

SELECT
    u.id,
    u.name,
    c.client_total,
    p.products_total
FROM users u
LEFT JOIN
   (SELECT user_id, COUNT(id) AS client_total FROM client GROUP BY user_id) c
    ON u.id = c.user_id
LEFT JOIN
   (SELECT user_id, COUNT(id) AS products_total FROM products GROUP BY user_id) p
    ON u.id = p.user_id

(Doesn’t even some people call these derived tables inline views?)

Advertisement