Bas Bas - 2 months ago 8
SQL Question

MySQL Query with properties and inner joins

I have 3 tables and testdata, see below.

How can i get this output doing a query?

Itemname Name Address
========================================
Test Item 1 test name 1 test address 1
Test Item 2 test name 2 test address 2


Table
Item
:

id Name
========================================
1 - Test Item 1
2 - Test Item 2


Table
Itemproperties
:

id item_id property_id value
======================================
1 1 1 test name 1
1 1 2 test address 1
1 2 1 test name 2
1 2 2 test address 2


Table
Properties
:

id name
===========
1 name
2 address

Answer

You will need to join the Itemproperties table twice, once for the name and once for the address:

SELECT
  i.name Itemname,
  ip1.value Name,
  ip2.value Address
FROM
  Item i
  JOIN Itemproperties ip1 ON i.id = ip1.item_id AND ip1.property_id = 1
  JOIN Itemproperties ip2 ON i.id = ip2.item_id AND ip2.property_id = 2