CODEWITHSUNDEEP
mysqlsql
J Doe
J Doe

Reputation: 3

How do I select values where those values are not used on another row?

This is my table t1

| postid | type | parentid |
| 1      |  Q   |   null   |
| 2      |  A   |   1      |
| 3      |  Q   |   null   |
| 4      |  Q   |   null   |
| 5      |  A   |   5      |
| 6      |  Q   |   null   |
| 7      |  Q   |   null   |
| 8      |  Q   |   null   |
| 9      |  A   |   8      |
| 10     |  A   |   8      |
| 11     |  Q   |   null   |
| 12     |  Q   |   null   |
| 13     |  Q   |   null   |
| 14     |  A   |   13     |
| 15     |  A   |   13     |

So in the table above, 1 is the post id and a parent Q and it is the parent for A with the post ID 2

Now, I would like to identify post ID with type Q that is not a parent for anyone.

Like if you observe the table above, I would like to select 3, 6, 7, 11, 12. So, how do I write query to achieve the result above.

Upvotes: 0

Views: 43

Answers (3)

flyingfox
flyingfox

Reputation: 13506

You can use NOT IN to do it

SELECT * FROM yourtable t1 WHERE t1.postid NOT IN
(SELECT parentid FROM yourtable t2 WHERE parentid IS NOT NULL)

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272106

NOT EXISTS sounds right for this

SELECT *
FROM t1
WHERE type = 'q'
AND NOT EXISTS (
    SELECT 1
    FROM t1 AS x
    WHERE x.parentid = t1.postid
)

Upvotes: 1

Evgeniy Belov
Evgeniy Belov

Reputation: 358

SELECT t1.*
FROM table t1
LEFT JOIN table t2 ON t1.postid=t2.parentid 
WHERE t2.postid is null AND t1.type='Q'

Upvotes: 1

Related Questions