CODEWITHSUNDEEP
mysql
Francis
Francis

Reputation: 147

select only one row from multiple rows MySql

I have a orders table as follows:

id | cust_id| status
1     25         1
2     25         1
3     25         0
4     26         0
5     26         1
6     26         0
7     27         1
8     27         1
9     27         1

I need two queries:

1. fetch all the 'cust_id' having at least one status 0.

eg. in above table I should get cust_id with two rows 25 and 26 because they have at least one 0 in 'status' column.

2. fetch all the 'cust_id' having all status 1.

eg. in above table I should get cust_id with one row 27 because it has all 1's in the 'status' column.

Upvotes: 0

Views: 2464

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

Assuming status is numeric 

select distinct cust_id 
from my_table where status  = 0 


select disctint cust_id
from my_table  
where cust_id NOT in (select distinct cust_id 
from my_table where status  = 0 )

these are the two queries ..

if you need both result in same select you can use union 

select distinct cust_id 
from my_table where status  = 0 
union 
select disctint cust_id
from my_table  
where cust_id NOT in (select distinct cust_id 
from my_table where status  = 0 )

Upvotes: 1

apomene
apomene

Reputation: 14389

SELECT DISTINCT cust_id FROM orders WHERE status=0
UNION 
SELECT DISTINCT cust_id FROM orders WHERE status=1 
AND cust_id NOT IN (SELECT cust_id FROM orders WHERE status<>1)

Upvotes: 0

Related Questions