0

I have these following tables:

suppliers

id | name
-----------
1  | sample

addresses

id | address | owner_type | owner_id
-------------------------------------
1  | adres   | Supplier   | 1

phone_numbers

id | number | owner_type | owner_id | phone_type
------------------------------------------------
1  | 12345  | Supplier   | 1        | phone
2  | 67890  | Supplier   | 1        | fax

It seem not a problem for me for addresses table, I use this query, I tested it and it work fine.

Select suppliers.*, addresses.address AS address FROM suppliers LEFT JOIN addresses ON (addresses.owner_id = suppliers.id AND addresses.owner_type = 'Supplier')

Now the problem is in phone_number table, there is 2 record with 2 different conditional, as you can see:

id | number | owner_type | owner_id | phone_type
------------------------------------------------
1  | 12345  | Supplier   | 1        | phone
2  | 67890  | Supplier   | 1        | fax

There is phone_type field -> phone and fax, and I want to show both of that in result, like this:

id  | name   | address | phone | fax
---------------------------------
1   | sample | adres   | 12345 | 67890

I try this query:

LEFT JOIN phone_numbers ON (phone_numbers.owner_id = suppliers.id AND phone_numbers.owner_type = 'Supplier' AND phone_type = 'phone')

Unfortunately that query just for 1 condition (phone) , how I get the fax value?

I use PostgreSQL.

Biffen
  • 6,249
  • 6
  • 28
  • 36
azy
  • 153
  • 1
  • 11
  • Do a second left join on the phone_numbers table but this time use the phone_type of 'fax'. – Bobby Sep 24 '14 at 07:57
  • Let try the below link this may help you, http://stackoverflow.com/questions/12169016/simple-pivot-sample – RickyRam Sep 24 '14 at 08:05

1 Answers1

0

You can join the phone_numbers tables two times using different alias.One for phone and one for fax. Like below P1 for phone and P2 for fax. 

Select suppliers.*, addresses.address AS address ,P1.number as 'Phone', P2.number as 'Fax'
FROM suppliers 
LEFT JOIN addresses ON (addresses.owner_id = suppliers.id AND addresses.owner_type = 'Supplier')
LEFT JOIN phone_numbers P1 ON (P1.owner_id = suppliers.id AND P1.owner_type = 'Supplier' AND P1.phone_type = 'phone')
LEFT JOIN phone_numbers P2 ON (P2.owner_id = suppliers.id AND P2.owner_type = 'Supplier' AND P2.phone_type = 'fax')
Priyank
  • 1,353
  • 9
  • 13
  • wow,, its fantastic, its work very fine, thank you so much bro,,,, – azy Sep 24 '14 at 08:07
  • For more complex situations where number of types (such as phone_type) are more or variable (not fixed), you can explore pivot functionality for converting rows values to columns values. – Priyank Sep 24 '14 at 08:43