CODEWITHSUNDEEP
mysql
Marco
Marco

Reputation: 2737

Retrieve values using a self join query

I want to retrieve records where the albumid is not found on the parent_albumid and where the parent_albumid is NULL

example:

albumid       name         parent_albumid
------------------------------------------------------
145           sun          NULL
146           fly          145
148           ok           NULL
149           mama         148
150           test         NULL

In the example above, albumid 145 and 148 are in the parent_albumid, so there are out. Record 146 and 149 have a parent_albumid, so there are out.... albumid 150 is the one left, the one that should be return by the result set

I was able to experiment using the self join query, but that doesn't help me at all!

SELECT a.albumid, a.name, a.parent_albumid
FROM album AS a
JOIN album AS b on a.albumid = b.parent_albumid

How do i create my self join query?

Upvotes: 0

Views: 64

Answers (1)

plalx
plalx

Reputation: 43718

There are many ways to do this, but here's one:

SELECT a.albumid
FROM album a
WHERE 
    parent_albumid IS NULL
    AND NOT EXISTS (
        SELECT 1
        FROM album
        WHERE parent_albumid = a.albumid
    )

Here's another one (I prefer the first which indicates better the intention):

SELECT a1.albumid
FROM album a1
LEFT JOIN album a2
    ON a2.parent_albumid = a1.albumid
WHERE a1.parent_albumid IS NULL AND a2.albumid IS NULL

Upvotes: 2

Related Questions