如何外部联接两个表(主表和多对一子表)以仅从第二个表中获得一项? [英] How to outer-join two tables (main and many-to-one sub-table) to get only ONE item from second table?

问题描述

我有两个这样的表:

主表:id(int)，标题(varchar)等. 子表:main_table_id(主表中的外键)，标记(varchar)等.

Main table: id (int), title (varchar), etc. Sub-table: main_table_id (foreign key into main table), tag (varchar), etc.

主表中给定的行可以有零个或多个子表行.

There can be zero or more subtable rows for a given row in the main table.

我想执行一个查询，该查询将返回主表的每一行，主表的列以及子表的仅一行(与哪一行无关)的列，如果这些列中有任何内容，否则为NULL.

I want to do a query that will return every row of the main table, with the columns of the main table, and a the columns from only a single row (doesn't matter which) of the sub-table, if there are any, otherwise NULL in those columns.

当然，如果我只是做一个基本的LEFT OUTER JOIN，那么我当然会多次重复执行主表，子表中的每个匹配项都会重复一次.

Of course if I just do a basic LEFT OUTER JOIN then of course I get the main table repeated multiple times, one for each match in the sub-table.

我敢肯定，在使用LEFT OUTER JOIN之前，我已经看到了这一点，并且采用了某种技巧，迫使从子表中仅选择一行，而不是全部—也许选择最小或最大OID.但是，一个多小时的谷歌搜索没有产生任何解决方案.

I'm sure I have seen this done before using a LEFT OUTER JOIN and some sort of trickery that forces only one row to be selected from the sub-table, not all of them -- maybe picking out the minimum or maximum OID. However, more than an hour of googling has not yielded any solutions.

有人在工具带中有这个窍门吗?

Does anybody have this trick in their toolbelt?

推荐答案

我最喜欢查理的答案，但是我不确定Postges是否具有TOP/LIMIT函数，因此这是一个不需要的替代解决方案(但确实假设sub_table有一个名为"id"的主键):

I like Charlie's answer best, but i'm not sure if Postges has a TOP/LIMIT function, so here's an alternate solution that doesn't need one (but does assume sub_table has a primary key called "id"):

SELECT * 
FROM main_table m LEFT OUTER JOIN sub_table s 
ON s.main_table_id = m.id
WHERE s.id IS NULL OR s.id IN ( 
  SELECT MAX(id) FROM sub_table GROUP BY main_table_id
)

这篇关于如何外部联接两个表(主表和多对一子表)以仅从第二个表中获得一项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！