Mysql query with parents properties

Question

I have three tables:

categories:

|Id | TopId|
| 1 | null |
| 2 | null |
| 3 |  1   |
| 4 |  1   |
| 5 |  2   |
| 6 |  3   |

etc...

categorycultures:

|Id|CultureOf|CultureId|Name      |
|1 |    1    |    1    |Category 1|
|2 |    1    |    2    |Klasse   1|
|3 |    1    |    3    |Kategori 1|
|4 |    2    |    1    |Category 2|
|5 |    2    |    2    |Klasse   2|
|6 |    2    |    3    |Kategori 2|

CultureOf means also: CategoryId etc...

cultures:

|Id|Name|Flag          |    
|1 |en  |/images/en.png|    
|2 |de  |/images/de.png|    
|3 |tr  |/images/tr.png|

and this sql script is getting what I need but without null top ids.

select c.Id as Id, cp.Id as TopId, cc.Name as Name, ccp.Name as TopName,    cul.Id as CultureId, cul.Name as CultureName

from categories as c

inner join categories as cp on c.TopId = cp.Id 

inner join categorycultures as cc on cc.CultureOf = c.Id

inner join categorycultures as ccp on ccp.CultureOf = cp.Id

inner join cultures as cul on cul.Id = cc.CultureId and cul.Id = ccp.CultureId

where cul.Id = 2

results are :

there are missing rows which those TopId's are null

I've tried lot of case when series on inner join for cp and tried many scripts but I cant get the correct values.

score 0 · Answer 1 · answered Dec 13 '17 at 17:42

0

try this

RIGHT join cultures as cul

answered Dec 13 '17 at 17:42

Gadsweb

53
10

score 0 · Accepted Answer · answered Dec 13 '17 at 18:46

This worked. but I didn't learned anything. I'll copy & paste next time I need this.

select c.Id as Id, c.TopId as TopId, cc.Name as Name, CASE
WHEN c.TopId is null THEN "none" 
ELSE ccp.Name
END as TopName, cul.Id as CultureId, cul.Name as CultureName

from categories as c

left join categories as cp on CASE
    WHEN c.TopId is null THEN "null" 
    ELSE c.TopId = cp.Id
END

inner join categorycultures as cc on cc.CultureOf = c.Id

left join categorycultures as ccp on ccp.CultureOf = cp.Id

left outer join cultures as cul on cul.Id = cc.CultureId and CASE
    WHEN c.TopId is null THEN 1 
    ELSE cul.Id = ccp.CultureId 
END

where cul.Id = 1

Mysql query with parents properties

2 Answers2