user3770963 user3770963 - 1 year ago 72
SQL Question

inner join, right join ? Count rows

How can I discover which orders has no details rows ?

Table Orders: orderId (int), customerName, deliveryDate (date), price (decimal)

Table Details: detailId (int), orderId (int), sku (varchar), quantity (int)

I tried (with no success) :

COUNT(Details.detailId) AS Tot,
FROM Details
ON Details.orderId = Orders.orderId
GROUP BY Details.orderId
HAVING Tot = 0

Answer Source

Try using a LEFT OUTER JOIN join. See also How to retrieve non-matching results in mysql .

INNER JOINS select the rows from two tables that match. Everything else will be discarded and is not available in the result set that you can query with HAVING

LEFT OUTER JOINS select a row for every entry in the left table (Orders). Always generate a row for the right table (Details). If no matching row can be found the values will be NULL. We can use this to our advantage, try:

SELECT Orders.orderId, Details.orderId
FROM Orders
LEFT OUTER JOIN Details              -- generate a row for every row in a
                                     -- make null if not matching
ON Details.orderId = Orders.orderId  -- match condition
WHERE Details.orderId IS NULL