Reputation: 1226

Suggest a MySQL Query

I have a table with 3 fields.

ID    Name     ParentID     Active
1     A        0            1
2     B        0            1
3     C        2            1

Now, I want a query where if there is row with ParentID > 0 and active then the row with its parent id (2) is skipped.

Please suggest a single MySQL query to achieve this.

Thank you, Khuram

Edit: Thanks guys, finally solved and marked the correct answer.

Upvotes: 0

Answers (4)

Reputation: 2871

this should work :

select * from test where id not in ( select parent_id from test where parent_id > 0 and active = 1 );

Upvotes: 2

Reputation: 12973

SELECT t1.*
FROM tbl t1
LEFT JOIN tbl t2
    ON t1.ID = t2.ParentID
    AND t2.Active = 1
WHERE t2.id IS NULL

Upvotes: 1

Reputation: 20439

I'm not sure the question is very clear. Do you mean read all rows where their ID is not in a parent column whose row is active? If so:

SELECT * FROM mytable
WHERE id NOT IN (
    SELECT ParentID FROM mytable WHERE Active = 1
);

Upvotes: 1

Reputation: 31239

Maybe something like this:

SELECT * FROM Table1 WHERE (NOT ParentID > 0 AND Active=1) OR Active=0

Upvotes: 1