CODEWITHSUNDEEP
mysqljoinsubquery
Didi Bui
Didi Bui

Reputation: 643

Writing subqueries instead of Joins MySQL

Here I have 2 tables in the a_bkorders database.

mysql> select *
    -> from customers;
+---------+----------------+-----------------+------------+------------------+----------------`enter code here`
| cust_id | cust_name_last | cust_name_first | cust_state | cust_postal_code | 
+---------+----------------+-----------------+------------+------------------+----------------

and

mysql> select *
    -> from order_headers;
+----------+------------+---------+
| order_id | order_date | cust_id |
+----------+------------+---------+

I need to show the cust_id, the cust_name and the number of order (count(order_id) as 'number of orders) but by using using subqueries, not joins.

This this what I wrote:

SELECT cust_id, cust_name_last,'number of orders'
FROM 
    (
    SELECT cu.cust_id, cu.cust_name_last, count(oh.order_id) as 'number of orders'
    FROM a_bkorders.customers cu
    JOIN a_bkorders.order_headers oh ON cu.cust_id = oh.cust_id
    WHERE cu.cust_state in ('NJ', 'MA')
    ) A;

and I get:

+---------+----------------+------------------+
| cust_id | cust_name_last | number of orders |
+---------+----------------+------------------+
|  208950 | Adams          | number of orders |
+---------+----------------+------------------+

But if I run the subquery alone, I only get 1 row out. (I know there are plenty)

+---------+----------------+------------------+
| cust_id | cust_name_last | number of orders |
+---------+----------------+------------------+
|  208950 | Adams          |               70 |
+---------+----------------+------------------+

So my question is why is the subquery alone only spits out one row instead of multiple rows. Also, am i joining the two tables correctly with a subquery and why do I get number of orders when I run the whole query?

Thank you for your help in advance,

Didi

Upvotes: 0

Views: 37

Answers (1)

ManojVenkat
ManojVenkat

Reputation: 484

There are few things you would want to correct in your query

  • In the sub-query, give a clear alias for the count variable instead of 'number of orders' and
  • Use that alias as the column name to be displayed (name the alias as you want to display)
  • most importantly use the group by clause to get the desired results (you are using an aggregate count)

Below is the modified query,

SELECT cust_id, cust_name_last, count as 'number of orders'
FROM 
(
SELECT cu.cust_id, cu.cust_name_last, count(oh.order_id) as count
FROM a_bkorders.customers cu
JOIN a_bkorders.order_headers oh ON cu.cust_id = oh.cust_id
WHERE cu.cust_state in ('NJ', 'MA')
GROUP BY oh.cust_id
) A;

Hope this helps!

Upvotes: 1

Related Questions