0

I have 2 tables one listing people and their relationships to their properties and a table of the list of properties (firstname, lastname etc).

Each person in the people table may not have all the properties listed in the properties table. What I want is each person returned with a row for every property if they have a link to it or not.

So for instance

Properties              Person
prop_id|prop_name       person_id|prop_id|prop_value
-----------------       -----------------------------------
1      |firstname        10      | 1            | john
2      |lastname         11      | 1            | sean
                         11      | 2            | connery

The result I want is this

10 | firstname | john
10 | lastname  | null
11 | firstname | sean
11 | lastname  | connery

Note that john/10 has a last name row but is null.

I thought this query would give me the result but it does not give me the null row for john that I desire.

select person_id, prop_value, prop_name
from person
left outer join properties on person.prop_id = properties.prop_id
union
select person_id, prop_value, prop_name
from person
right outer join properties on person.prop_id = properties.prop_id

This query omits the 10 lastname null row. Is this query possible? This is MySQL/MariaDB.

Gurnard
  • 1,773
  • 22
  • 43

1 Answers1

2

I think you want to join the person table to the properties table twice to get this to work. Something like this maybe:

SELECT DISTINCT p1.person_id, p2.prop_value, prop.prop_name
FROM person p1
  CROSS JOIN properties prop
  LEFT JOIN person p2 ON p1.person_id = p2.person_id AND prop.prop_id = p2.prop_id

This is demonstrated working in @RomainBar's sqlfiddle.com/#!9/3dadb90/7

cf_en
  • 1,661
  • 1
  • 10
  • 18
  • 2
    It will work if you add a `DISTINCT` to @cf_en query : http://sqlfiddle.com/#!9/3dadb90/7 If you don't use 2 joins you can't have person_id not null and prop_value null as they are from the same table – Romain B. Nov 09 '17 at 15:08
  • Thanks for the note on distinct and the sqlfiddle. I've added the `DISTINCT` into the answer. – cf_en Nov 09 '17 at 15:13