0

I have 3 tables: device, device_properties, and property. The device_properties tables accomplishes a many-to-many relationship between the device and property tables.

In the sample data below, the device named ABC-2 only has 3 properties, but ABC-1 has 4 properties (as described in the device_properties table):

device         device_properties          property    

id  name       device_id property_id      id   name   value
1   ABC-1       1        1                1    height  20cm
2   ABC-2       1        3                2    height  30cm
                1        4                3    weight  100g
                1        5                4    colour  black
                2        2                5    width   5cm
                2        3                6    width   10cm
                2        6

How can I retrieve all results from the tables in the following format, given that there are some properties missing for some devices (ABC-2 is missing a colour in this example) ?

deviceId  deviceName deviceHeight deviceWeight deviceColour deviceWidth
1         ABC-1      20cm         100g         black        5cm
2         ABC-2      30cm         100g         ----         10cm

Thanks in advance for any help!

bwrabbit
  • 529
  • 1
  • 4
  • 25
  • Join the tables and then pivot the results to get the properties in columns. – Barmar Feb 08 '18 at 23:14
  • @bwrabbit Have you even done some research???? There are tons of example on the net. Research before asking question!!! – Eric Feb 08 '18 at 23:18
  • @Eric I don't really know where to look so if you have anything useful to say it would be helpful to suggest some things to search for rather than throw out the redundant punctuation marks in frustration. I have tried and am getting no where. Will look into pivot tables as per Barmars comment. – bwrabbit Feb 08 '18 at 23:54

1 Answers1

0

try this

select
  d.id deviceId,
  d.name deviceName,
  case when p.name='height' then p.value else null end deviceHeight,
  case when p.name='weight' then p.value else null end deviceWeight,
  case when p.name='colour' then p.value else null end deviceColour,
  case when p.name='width' then p.value else null end deviceWidth
from
  device d
  join device_properties dp on dp.device_id=d.id
  join porperty p on p.id=dp.property_id
guigoz
  • 674
  • 4
  • 21