CODEWITHSUNDEEP
mysqlsqljoin
user3770963
user3770963

Reputation: 47

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) :

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

Upvotes: 0

Views: 472

Answers (3)

somnium
somnium

Reputation: 1547

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270653

How about not in or not exists?

select o.*
from orders o
where not exists (select 1
                  from details d
                  where d.orderId = o.orderId
                 );

If you don't want all columns, then select the ones you want in the outer select.

Upvotes: 4

silly
silly

Reputation: 7887

select it with a outer join an check if orderId is null... like this

SELECT
    o.*
FROM orders o
LEFT OUTER JOIN details d
    ON d.orderId = o.orderId
WHERE d.orderId IS NULL

Upvotes: 0

Related Questions