Match Id from one table with name from another and null values - mimer sql

Question

I want to bring a column from another table that matches my id from the current table.

I've done this query:

SELECT dept, SUM(quantity) as TotalQuantity 
FROM sale 
GROUP BY dept 
ORDER BY TotalQuantity;

Which gives me a total of items sold of a specific "department", now I want to bring the name of the department in there WITHOUT and WITH using explicit join. The department name is in another table called "dept" with column "name". Is there also possible the get the null values of my "sale" table as well?

SELECT dept, SUM(quantity) as TotalQuantity 
FROM sale 
GROUP BY dept 
ORDER BY TotalQuantity;

Welcome to StackOverflow. Please, include the DDL for the data and a simple example with the expected results. Also... I have formatted both queries but are showing the same query, so, I think there is an erratum in your first version. — Angel M., Jan 06 '19 at 12:22

score 1 · Answer 1 · edited Jan 06 '19 at 19:21

The correct way is to use an explicit JOIN:

SELECT d.name, d.dept, SUM(s.quantity) as TotalQuantity 
FROM dept d JOIN
     sale s
     ON d.dept = s.dept
GROUP BY d.name, d.dept 
ORDER BY TotalQuantity;

If you want all rows in from dept, even those without sales, use a LEFT JOIN.

Note the use of table aliases and qualified column references. These are best practices when a query refers to more than one table.

I would never advise anyone to use a comma in a FROM clause. It is simply bad form. You can use a subquery if you want:

SELECT (SELECT d.name
        FROM dept
        WHERE d.dept = s.dept
       ) as name
       s.dept, SUM(s.quantity) as TotalQuantity 
FROM sale s
GROUP BY s.dept 
ORDER BY TotalQuantity;

Or, alternatively:

SELECT d.dept, d.name,
       (SELECT SUM(s.quantity)
        FROM sales s
        WHERE s.dept = d.dept
       ) as TotalQUantity
FROM dept d
ORDER BY TotalQuantity;

This is equivalent to the LEFT JOIN version -- it keeps all departments. With the right indexes (on sales(dept, quantity)), this can even be the fastest version.

GMB · Answer 2 · 2019-01-06T13:12:38.787

0

Using an explicit JOIN (assuming that column sale.dept references dept.id) :

SELECT d.id d.name, SUM(s.quantity) AS TotalQuantity 
FROM dept AS d
LEFT JOIN sale AS s ON d.id = s.dept
GROUP BY d.id d.name
ORDER BY TotalQuantity

Because of the LEFT JOIN, departments without sales will also appear in the result list.

This would not be very relevant to do the same query using implicit, old-style JOIN, but that would be :

SELECT d.id d.name, SUM(s.quantity) AS TotalQuantity 
FROM sale AS s, dept AS d
WHERE d.id = s.dept
GROUP BY d.id d.name,
ORDER BY TotalQuantity

edited Jan 06 '19 at 13:12

answered Jan 06 '19 at 12:24

GMB

216,147
25
84
135

Thanks alot for your quick answer but it gave me some error: Mimer SQL error -12202 in function PREPARE d.id is not a column of an inserted table, updated table or any table identified in a FROM clause – Boohz12 Jan 06 '19 at 12:27
@Boohz12 : I said I assumed `dept` has a column `id`, but is this the case ? What are the available columns in table `dept` ? – GMB Jan 06 '19 at 12:29
To make sure, first one is with explicit and second is without? – Boohz12 Jan 06 '19 at 12:32
1

First is explicit JOIN, second is implicit JOIN – GMB Jan 06 '19 at 12:33
thank you :) do you know how to use the same query but also to present departments that haven't sold any items, with null as the number of items they have sold? – Boohz12 Jan 06 '19 at 12:35

score 0 · Answer 3 · answered Jan 06 '19 at 12:41

0

I also want to present the departments that haven't sold any items as well with the null as the number of items they have sold:

Is this the correct way?

SELECT s.dept, d.name, SUM(s.quantity) as TotalQuantity 
FROM sale s, dept d
WHERE d.number = s.dept AND s.quantity IS NULL
GROUP BY s.dept, d.name
ORDER BY TotalQuantity;

answered Jan 06 '19 at 12:41

Boohz12

19
5

Or does this give me a separate result, I want null values to be in the same result as the other result(that shows me number of items each department have sold) – Boohz12 Jan 06 '19 at 12:44
Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed! – jarlh Jan 06 '19 at 19:04

Match Id from one table with name from another and null values - mimer sql

3 Answers3