SQL query to join 3 tables and return a single row

Question

I have a table with a list of places - each are unique based on the place_id field:

place_id    name
TEST01  Company1

I also have a table called place_address which has an index field called address_id which is then used to store the address information (Note the place has multiple addresses):

place_id    address_type        address_id
TEST01      DEFAULT             507
TEST01      REGISTERED_OFFICE   508

The address table actually holds the address details:

address_id  name         address             city

507         address1    address1, road1      city1
508         address2    address2,road2       city2

I am querying the three tables using 2 joins:

select  pl.place_id,
        pl.name,
        pl.email_address,
        pl.phone,
        pl.fax,
        pl.web_address,
        pl.vat_registration,
        pl.user_def2 as companyregno,
        case when pa.address_type = 'DEFAULT' then ad.address else NULL end as default_address,
        case when pa.address_type = 'REGISTERED_OFFICE' then ad.address else NULL end as registered_address,
        ad.second_address,
        ad.third_address,
        ad.fourth_address,
        ad.city,
        ad.zippost
from place pl 
left join place_address pa on pl.place_id = pa.place_id
left join address ad on pa.address_id = ad.address_id
where pl.place_id like '%TEST%'
and pa.address_type in ('DEFAULT','REGISTERED_OFFICE')

The output is as follows:

place_id    name    email_address   phone   fax web_address vat_registration    companyregno    default_address registered_address  second_address  third_address   fourth_address  city    zippost
TEST01  Company1    NULL    NULL    NULL    NULL    2345678 123456  address1, road1 NULL    NULL    NULL    NULL    city1   NULL
TEST01  Company1    NULL    NULL    NULL    NULL    2345678 123456  NULL    address2,road2  NULL    NULL    NULL    city2   NULL

However, what I am trying to achieve is to have a single line output where the default address and the registered address are on the single line output:

place_id    name    email_address   phone   fax web_address vat_registration    companyregno    default_address registered_address  second_address  third_address   fourth_address  city    our_zippost
TEST01  Company1    test@test.co.uk 0123456789  0123456789  test@test.com   2345678 123456  address1,road1  address2,road2  NULL    NULL    NULL    NULL    NULL

Any help would be greatly appreciated in achieving this!

Answer 1

Something like this should work for your case:

SELECT a.place_id, a.name, add_for_def.address as 'DEFAULT_ADDRESS', add_for_reg.address as 'REGISTERED_OFFICE_ADDRESS'
FROM places a
INNER JOIN place_address def_add ON def_add.place_id = a.place_id AND def_add.address_type = 'DEFAULT'
INNER JOIN place_address reg_off ON reg_off.place_id = a.place_id AND  reg_off.address_type = 'REGISTERED_OFFICE'
INNER JOIN address add_for_def ON add_for_def.address_id = def_add.address_id
INNER JOIN address add_for_reg ON add_for_reg.address_id = reg_off.address_id
WHERE a.place_id = 'TEST_01'

Basically, you are doing a join on the same table twice, but each join is constrained with the ADDRESS_TYPE column. Then you make another JOIN to the actual ADDRESS table which would allow for you to display both addresses in the same line.

P.S.

• I have omitted some columns for brevity.
• You may also add the additional where clause as required.