MySQL: Get dates with and without category/subcategory in ONE query (and sorted)

Question

I have a database with 4 tables with this structure:

• categories
• subcategories
• dates
• events

We have events, that can have multiple dates. Events are categorized in categories and subcategories, but can have only a category and no subcategory, too.

I tried this query:

SELECT
    t.id as sortid,
    t.numprint,
    s.titel,
    s.intro,
    s.inhalte,
    s.zielgruppe,
    s.methoden,
    s.kapitelprint,
    s.unterkapitelprint,
    t.ort,
    t.bundesland,
    t.email,
    t.telefon,
    t.preis,
    t.dateprint
FROM
    kapitel k
LEFT JOIN
    unterkapitel u
    ON u.parent = k.id
LEFT JOIN
    seminare s
    ON s.kapitel = k.id
    AND s.unterkapitel = u.id
    AND s.aktiv = 1
LEFT JOIN
    termine t
    ON t.parent = s.id

But this doesn't get the events with no subcategory - they all have NONE in all fields. Is there a way to get all dates in one query?

Thanks in advance, Sebastian

Answer 1

OK, last shot:

if you want all kapitels, regardless of whether they have an event.

SELECT * 
FROM kapitel k

LEFT JOIN seminare s
ON s.kapitel = k.id
    AND s.aktiv = 1

LEFT JOIN termine t
ON t.parent = s.id

LEFT JOIN unterkapitel u
ON u.parent = k.id
    AND s.unterkapitel = u.id

If you want only events / siminare:

SELECT * 
FROM seminare s

JOIN kapitel k
ON s.kapitel = k.id
    AND s.aktiv = 1

LEFT JOIN termine t
    ON t.parent = s.id

LEFT JOIN unterkapitel u
ON u.parent = k.id
    AND s.unterkapitel = u.id

But I don't really like it that in theory is is possible a Seminar can have a Kapital & Unterkapitel which aren't related (which you can prevent in script of course), I keep thinking there should be a better layout for this, but the only thing I can think of is merging Kapitel & Unterkapitel into 1 table with a simple Adjancency Model, keeping you free to enter either an Kapitel or Unterkapitel in a single 'kapitel' field in Seminare, dropping the Unterkapitel field entirely.

Answer 2

Wrikken, thanks! But this doesn't return the correct order.

I need all events (termine) from all kapitel (categories), including their unterkapitel (subcategories), but in their correct order:

• sample date 1 with category a
• sample date 2 with category b, subcategory b.a
• sample date 3 with category b, subcategory b.a
• sample date 4 with category b, subcategory b.b
• sample date 6 with category b, subcategory b.c
• sample date 7 with category c, subcagetory c.a

... and so on.

ie.: go into the categories, return any dates without subcategory or go into the subcategories, loop trough all of them and return all dates from these subcategories.

at the moment I'm doing this logic inside my python script, but I thought this should be possible with MySQL directly.