CODEWITHSUNDEEP
mysqlsqljoinmultiple-tablesmultiple-join-rows
Tjab
Tjab

Reputation: 403

MySQL VIEW with morph type in where statement

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

Upvotes: 0

Views: 2180

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

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.

Upvotes: 0

Related Questions