Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sample data from the genres table:

id   | genre_name     | genre    | parent_id
---------------------------------------------
2    | Blues          | MAIN     | NULL
6    | Folk           | MAIN     | NULL
10   | Pop            | MAIN     | NULL
1032 | Easy Listening | SUBGENRE | 10
1067 | Pop Ballad     | SUBGENRE | 10
1068 | Pop Rock       | SUBGENRE | 10

Why doesn't the statements #1 and #2 (with the reflexive join) NOT return the parent?

-- Statement #1 only returns only the child rows.
SELECT
    pg.id,
    cg.*
    FROM `genres` AS pg
    LEFT JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id OR (pd.id= NULL AND pg.id = 10)
    WHERE pg.id = 10;
-- OUTPUT:
id | id   | genre_name     | genre    | parent_id
--------------------------------------------------
10 | 1032 | Easy Listening | SUBGENRE | 10
10 | 1067 | Pop Ballad     | SUBGENRE | 10
10 | 1068 | Pop Rock       | SUBGENRE | 10

-- Statement #2 only returns only the child rows.
SELECT
    pg.id,
    cg.*
    FROM `genres` AS pg
    LEFT JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id 
    WHERE pg.id = 10 AND pg.parent_genre_id IS NULL;

-- OUTPUT:
id | id   | genre_name     | genre    | parent_id
--------------------------------------------------
10 | 1032 | Easy Listening | SUBGENRE | 10
10 | 1067 | Pop Ballad     | SUBGENRE | 10
10 | 1068 | Pop Rock       | SUBGENRE | 10


-- Statement #3 returns both parent and child rows.
(SELECT
    id,
    genre_name,
    genre_type,
    parent_genre_id
    FROM `genres`
    WHERE id = 10)
UNION -- Get all sub-genres (children)
(SELECT
    cg.id,
    cg.genre_name,
    cg.genre_type,
    cg.parent_genre_id
    FROM `genres` AS pg
     JOIN `genres` AS cg
        ON pg.id = cg.parent_genre_id
    WHERE pg.id = 10);

-- OUTPUT:
id   | genre_name     | genre    | parent_id
---------------------------------------------
10   | Pop            | MAIN     | NULL
1032 | Easy Listening | SUBGENRE | 10
1067 | Pop Ballad     | SUBGENRE | 10
1068 | Pop Rock       | SUBGENRE | 10

The #3 statement seems like a bad use of UNION to me.

Warning, bad pun intended:

Now the term self-relationship sounds like it implies a certain thing people do with their hands below their belt; but in any case I would like to know why the first two selects do not cum up with the intended results. Rather disappointing after the query reached climax. Uh, I mean returns the results.


After the answer given below, I ended up using the UNION (no JOIN of course):

(SELECT 
        `id`,
        `genre_name`,
        `genre_type`,
        `parent_genre_id`
    FROM `genres`
    WHERE `id` = 10)
UNION -- Get all sub-genres (children)
    (SELECT
        `id`,
        `genre_name`,
        `genre_type`,
        `parent_genre_id`
        FROM .`genres`
        WHERE `parent_genre_id` = 10);
share|improve this question
    
I think the first two queries have a mistake. On #1 pd.id doesn't exist because pd is not an alias of anything as far as I can tell. Also = NULL is never going to work because NULL is never equal to anything (unless MySQL has different NULL semantics to civilised DBMS platforms). On #2 the AND pg.parent_genre_id IS NULL isn't going to add any more selectivity to the query on the data set. –  ConcernedOfTunbridgeWells Mar 26 '12 at 12:31
    
Yes you are correct, I was modifying and testing the query when I was posting the question. I got rid of the OR (pd.id= NULL AND pg.id = 10), the 'pd' was supposed to be a 'pg'. The AND pg.parent_genre_id IS NULL was just a desperation move, since I'm a NOVICE SQL'r. –  b01 Mar 26 '12 at 13:36

1 Answer 1

up vote 1 down vote accepted

See the comment on the question about #1 and #2.

I don't believe MySQL supports recursive CTEs, so a union is likely to be the best way to do this.

The self joins are only going to work where the parent key is not null, because NULL will never evaluate as equal to anything (unless MySQL has weird NULL semantics). In order to capture the parent the union is a reasonable approach. Left joins for this are barking up the wrong tree because you can only specify them as a join predicate for the child and you're trying to include data from a parent row that actually has no join against itself.

Another approach would be to make parent_genre_id equal to id on the pop (id = 10) row, so the self join would also pick up the parent. You can tell the parent because the id and parent_genre_id keys are the same value.

share|improve this answer
    
Yes, only after I got to work this morning did I realize that I did NOT need the JOIN in statement #3. Also I'm a novice SQL'r, can you explain, point me to an explanation, or give an simple example of how to make parent_genre_id equal to the id on the pop( id = 10 ). –  b01 Mar 26 '12 at 13:43
    
@b01 - I mean by recording it in the table that way in the first place, rather than recording NULL. If you don't have the option of doing that, use the UNION. –  ConcernedOfTunbridgeWells Mar 26 '12 at 13:45
    
UNION is the way to go for now then. I'm currently working of the off stating databases, and those cannot be modified. However, the stating database get merged into a production database, if the UNION turns out to be less perform-ant than the reflexive join, I could possibly make that modification during the merge. –  b01 Mar 26 '12 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.