CODEWITHSUNDEEP
sqlselectcount
Jimmy Olsson
Jimmy Olsson

Reputation: 11

SQL only show orders with one order row. if more rows don display anything

i´m trying to search for orders with one specific article on the order row, but if there more than one row i don´t want to see any of the rows.

This is a sample of two orders where i only want to get one resault. order 9891026 has two rows and 9891025 only have one row.

    select  order_no, line_no
    from customer_order_line
    where order_no in('9891026','9891025')

result

order_no line_no
9891026 1
9891026 2
9891025 1

i only want to get

order_no line_no
9891025 1

I have don´t have a clue how to make the right query for this.

Upvotes: 0

Views: 199

Answers (3)

jarlh
jarlh

Reputation: 44776

Alternatively, you can do a GROUP BY and use HAVING to make sure only one row is there.

select ol.order_no, min(ol.line_no)
from customer_order_line ol
where ol.order_no in ('9891026', '9891025')
group by ol.order_no
having count(*) = 1

Upvotes: 0

Stefanov.sm
Stefanov.sm

Reputation: 13049

First find the single-line order numbers order_no (the subquery) and then select from orders with these order numbers only. Please note that a join query (though maybe harder to read) might be more efficient.

select * 
from customer_order_line 
where order_no in
(
 select order_no 
 from customer_order_line
 group by order_no 
 having count(*) = 1
) -- this is the list of single-item orders
and order_no in (9891026,9891025) -- this is the list of target orders

Upvotes: 0

Stu
Stu

Reputation: 32609

One way would be to check if an order exists with a line_no greater than 1:

select ol.order_no, ol.line_no
from customer_order_line ol
where ol.order_no in (9891026, 9891025)
and not exists (
  select * from customer_order_line x
  where x.order_no = ol.order_no and x.line_no > 1
);

Upvotes: 1

Related Questions