0

I have the following tables. I've simplified the code for readability

CREATE TABLE entries (
    ID int(),
    entry varchar(),
    parent_id int()
);

CREATE TABLE nouns (
    entry_id int(),
    plural varchar(),
    [...]
);

CREATE TABLE verbs (
    entry_id int(),
    pres_part varchar(),
    past_part varchar(),
    [...]
);

CREATE TABLE definitions (
    ID int(),
    definition varchar(),
    [...]
);

CREATE TABLE definitions_entries (
    definition_id int(),
    entry_id int()
);

and I am displaying all the entries on a glossary page as follows

SELECT *
FROM entries
LEFT JOIN nouns ON nouns.entry_id = entries.ID 
LEFT JOIN verbs ON verbs.entry_id = entries.ID 
LEFT JOIN definitions_entries ON entries.ID = definitions_entries.entry_id 
LEFT JOIN definitions ON definitions_entries.definition_id = definitions.ID 
ORDER BY entries.entry

But I want to reorder the list so that child-entries appear beneath their parents but that parent entries are still sorted alphabetically. I image the output would look something like this...

1    entry a    null      definition 1
4    entry a    entry x   definition 2
5    entry a    entry y   definition 3
2    entry b    null      definition 4
5    entry b    entry z   definition 5
3    entry c    null      definition 6

I've tried to follow this and this and this but I'm not getting anywhere.

I don't have any SQL examples left to show because they've all just gone to shit and I'm lost and confused. The best result I got looked something like this though...

4    entry a    entry x   definition 2
5    entry a    entry y   definition 3
5    entry b    entry z   definition 5

1    entry a    null      definition 1    missing
2    entry b    null      definition 4    missing
3    entry c    null      definition 6    missing

I'm running MySQL 5.6 so I can't use RECURSIVE WITH. Not that I even understand if that would help me.

guardiancrescent
  • 1
  • 1
  • 1
    There's no self-join in your query. You're not doing anything with `parent_id` to show the hierarchy. – Barmar Apr 15 '20 at 23:22
  • You join on a parent table, but there is no parent table in your query. Your question does not indicate how many levels of hierarchy you can have. – Shadow Apr 15 '20 at 23:24
  • I realise I haven't self joined here. All my attempts failed. @Shadow that was just a mistake left over one of my failed attempts. I've updated the post – guardiancrescent Apr 16 '20 at 12:37

0 Answers0