MySQL VIEW with morph type in where statement

Question

I have the following tables:

Apps

id   name
1    a
2    b
3    c

Parts

id   name   app_id
1    x      1
2    x      2
3    y      2
4    z      1
5    z      2
6    z      3

Settings

id   name        morph_id   morph_type
1    setting 1   1          App
2    setting 1   2          App
3    setting 2   1          Part
4    setting 2   2          Part
5    setting 3   3          Part
6    setting 4   3          App
7    setting 5   5          Part

This means that App 1 (A), has Part 1 and 4 (X/Z), and Setting 1 and 2 (1 belongs to the app, 2 belongs to part 1).

I would like to generate a view, that displays this situation:

SettingsView

app_id   setting_type   setting_name   // app_name   part_id  part_name

1        App            setting 1      // a          null     null
1        Part           setting 2      // a          1        x

2        App            setting 1      // b          null     null
2        Part           setting 2      // b          2        x
2        Part           setting 5      // b          5        z

3        App            setting 4      // c          null     null
3        Part           setting 3      // c          3        z

I however have no idea on how to accomplish this and whether it is possible? The two columns after // would be nice if possible, as it would be useful to see to which app/part (name) a setting belongs.

Kind regards, Tjab

Another edit to explain a bit more:

SELECT a.id, s.name 
FROM settings s JOIN app a ON a.id = s.morph_id 
WHERE s.morph_type = 'App' #IMPORTANT

Shows all app settings. This I need to combine with the query:

SELECT p.app_id, s.name
FROM settings s JOIN part p ON p.id = s.morph_id
WHERE s.morph_type = 'Part' #IMPORTANT

@dbajtr a setting belongs to an app or a part, for instance "username" can be an app setting, where as "is_enabled" can be a part setting. The morph type shows me if it's a part or app setting. In my application multiple apps can be created (app a can be created by multiple customers), which all have their own app setting "username" and all have their own "is_enabled" setting for part a. — Tjab, Jun 30 '17 at 08:03

score 0 · Answer 1 · answered Jun 30 '17 at 08:19

0

You might try this:

SELECT 
    a.`id` as `app_id`,
    b.`morph_type` as `setting_type`,
    b.`name` as `setting_name`,
    a.`name` as `app_name`,
    c.`id` as `part_id`,
    c.`name` as `part_name`
FROM `Apps` a
LEFT JOIN `Settings` b
ON a.`morph_id` = a.`id`
LEFT JOIN `Parts` c
ON c.`app_id` = a.`id`
ORDER BY a.`name`,b.`name`

You may not need the LEFT JOIN on the Settings table. If you're getting nulls in the setting_name column, change it to a JOIN instead.

answered Jun 30 '17 at 08:19

Sloan Thrasher

4,953
3
22
40

Sadly, this doesn't give the correct result. I've got a setting per part, so app A has part 1 to 10, and they all have the setting "is_enabled". The setting is shown only once in the query. – Tjab Jun 30 '17 at 11:38

MySQL VIEW with morph type in where statement

1 Answers1