Joining with NULLs

Question

There are a lot of questions about joins and null, but i couldn't find one that matched this particular schema. I have 3 pretty simple tables.

+---------+
| service |
+---------+
| id      |
| name    |
+---------+

+-----------+
| propnames |
+-----------+
| id        |
| name      |
| sort      |
+-----------+

+----------+
| props    |
+----------+
| sid      |
| pid      |
| value    |
+----------+

I would like to be able to add a property (to propnames) and query my services, joining my properties (props) and know which properties haven't been set yet.

If this is services

(id), (name)
1, "AAA"
2, "BBB"

This is propnames

(id), (name), (sort)
1, "property_a", 1
2, "property_b", 2
3, "property_c", 3

This is props

(service.id), (propname.id), (value)
1, 1, "Service AAA has property_a value"
1, 2, "Service AAA has property_b value"
2, 1, "Service BBB has property_a value"

Then ultimately my query will produce this:

(service.id), (service.name), (property.id), (property.name), (props.value)
1, "AAA", 1, "property_a", "Service AAA has property_a value"
1, "AAA", 2, "property_b", "Service AAA has property_b value"
1, "AAA", 3, "property_c", NULL
2, "BBB", 1, "property_a", "Service BBB has property_a value"
2, "BBB", 2, "property_b", NULL
2, "BBB", 3, "property_c", NULL

IDEALLY, it would be sorted by service.name ASC -then- property.sort

Currently the incomplete query is:

SELECT s.id, s.name, p.id, p.name, props.value 
FROM service.s 
LEFT JOIN propnames p ON s.id = p.sid 
LEFT JOIN props ON props.pid = p.id 
ORDER BY s.name ASC, p.sort ASC

Answer 1

Use a cross join to generate the rows. Then use left join to find the matches:

select s.id, s.name, p.id, p.name, props.value 
from services s cross join
     propnames p left join
     props
     on props.sid = s.id and props.pid = p.id;