Mysql - 如何在左连接中为整个表添加别名

发布于 2024-12-01 08:07:10 字数 717 浏览 1 评论 0原文

我遇到的情况是，属性表保存地址 ID（来自 g_addresses 表），而申请人表也保存来自 g_addresses 的地址 ID。我想将它们左连接在一起，但选择表中的所有字段。

我知道使用“as”为字段创建别名，但是有没有办法为整个表生成别名？

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

这会产生仅包含一个地址行而不是两者的结果，返回的行是最终联接中的行，而不是之前的行，表明返回时会覆盖该行。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你在看孤独的风景 2024-12-08 08:07:10

我认为您不应该在您的情况下使用屏蔽引用，例如 * 或 `reference`.*，因为您最终可能会得到包含相同列的行集名称（id、address_id）。

如果要从连接表中提取所有列，您可能应该在 SELECT 子句中单独指定它们，并为每个列分配一个唯一的别名：

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

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

回复收藏 0 原文

江湖正好 2024-12-08 08:07:10

更具体地说明您选择的列

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

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

回复收藏 0 原文

~没有更多了~