mysql select query php based on column value

Question

i have a table like this,

i want to select all doc no having status 1. select * from tbl where doc_status=1 will select 101,102,101. But in for doc_no 101 there is doc-status 0 also so it wont select.How can i do that, desired output will be 103 only

plz try this. select * from tbl where doc_status='1' what is data type of doc_status — Niroshan Ranapathi
– Niroshan Ranapathi, Commented Aug 16, 2016 at 6:07
Select x.* from my_table x left join my_table y on y.doc_no = x.doc_no and y.status <> 1 where x.status = 1 and y.id is null — Strawberry
– Strawberry, Commented Aug 16, 2016 at 6:59

1000111 · Accepted Answer · 2016-08-16 06:22:38Z

2

You can use NOT EXISTS

SELECT 
DISTINCT T.doc_no
FROM tbl T
WHERE NOT EXISTS 
(
 SELECT 
 1
 FROM tbl TT
 WHERE TT.doc_status = 0
 AND TT.doc_no = T.doc_no
)

Working Demo

Note: I guess doc_status can hold value either 0/1. If this is the case then no need to filter with doc_status = 1 outside.

OR you can use NOT IN

SELECT 
 DISTINCT T.doc_no
FROM tbl T
WHERE T.doc_no NOT IN
(
 SELECT 
    TT.doc_no
 FROM tbl TT
 WHERE TT.doc_status = 0
)

edited Aug 16, 2016 at 6:22

answered Aug 16, 2016 at 6:07

1000111

13.5k2 gold badges32 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zakhefron · Accepted Answer · 2016-08-16 06:23:47Z

1

Try

SELECT * FROM test GROUP BY doc_no HAVING MIN(doc_status) = 1;

SQL Demo

answered Aug 16, 2016 at 6:23

zakhefron

1,4431 gold badge9 silver badges14 bronze badges

Collectives™ on Stack Overflow

mysql select query php based on column value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related