Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql - How to alias a whole table in a left join

I have a situation where a property table holds an address id (from the g_addresses table) and an applicant table also holds an address id from the g_addresses. I'd like to left join these together but select all the fields in the table.

I know of using 'as' to make an alias for fields, but is there any way to produce an alias for a whole table?

SELECT *
FROM (`reference`)
LEFT JOIN `applicants` ON `applicants`.`id` = `reference`.`applicant_id`
LEFT JOIN `g_people` applicant_person ON `applicant_person`.`id` = `applicants`.`person_id`
LEFT JOIN `g_addresses` applicant_address ON `applicant_address`.`id` = `applicants`.`address_id`
LEFT JOIN `properties` ON `properties`.`id` = `reference`.`property_id`
LEFT JOIN `g_addresses` property_address ON `property_address`.`id` = `properties`.`address_id`
WHERE `reference`.`id` = 4 

This produces a result containing only one address row and not both, The row that is returned is the row from the final join and not the one previously, indicating it is overwriting when it is returned.

like image 731
Anthony Avatar asked Aug 22 '11 16:08

Anthony


2 Answers

I don't think you should use masked references, like * or `reference`.*, in your case, because you may end up with a row set containing identical column names (id, address_id).

If you want to pull all the columns from the joined tables, you should probably specify them individually in the SELECT clause and assign a unique alias to every one of them:

SELECT
  ref.`id` AS ref_id,
  ref.`…`  AS …,
  …
  app.`id` AS app_id,
  …
FROM `reference` AS ref
LEFT JOIN `applicants`  AS app ON app.`id` = ref.`applicant_id`
LEFT JOIN `g_people`    AS ape ON ape.`id` = app.`person_id`
LEFT JOIN `g_addresses` AS apa ON apa.`id` = app.`address_id`
LEFT JOIN `properties`  AS pro ON pro.`id` = ref.`property_id`
LEFT JOIN `g_addresses` AS pra ON pra.`id` = pro.`address_id`
WHERE ref.`id` = 4
like image 123
Andriy M Avatar answered Oct 16 '22 14:10

Andriy M


Be more specific about columns you select

SELECT 
  applicant_address.*,
  property_address.*,
  applicants.*,
  applicant_person.*,
  properties.*
FROM (`reference`)
LEFT JOIN `applicants` ON `applicants`.`id` = `reference`.`applicant_id`
LEFT JOIN `g_people` applicant_person ON `applicant_person`.`id` = `applicants`.`person_id`
LEFT JOIN `g_addresses` applicant_address ON `applicant_address`.`id` = `applicants`.`address_id`
LEFT JOIN `properties` ON `properties`.`id` = `reference`.`property_id`
LEFT JOIN `g_addresses` property_address ON `property_address`.`id` = `properties`.`address_id`
WHERE `reference`.`id` = 4 
like image 39
Michael Berkowski Avatar answered Oct 16 '22 12:10

Michael Berkowski