对连接表中没有键的所有记录和具有匹配键的记录进行单个查询

发布于 2024-08-06 01:03:33 字数 934 浏览 3 评论 0原文

我有一次旅行，有很多驻地。我需要一个查询来返回未指定居住信息的所有行程。以及与指定居住地匹配的所有旅行。

我可以从此查询中获得第一个：

SELECT * FROM `trips` WHERE (((NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id))

但要获得第二个，我还需要添加这一点：

INNER JOIN `residencies` ON (`trips`.`id` = `residencies`.`trip_id`)

在 WHERE 子句之前添加联接会要求提供具有居住 ID 和不具有居住 ID 的结果。这显然没有返回任何结果。那么我该如何编写此代码才能在一个查询中获取完整的结果集呢？不允许使用存储过程。

我正在使用 Rails，因此如果答案是特定于 Rails 的，那么这是一个额外的好处（但绝对不是必需的）。如果有人能展示如何使用 searchlogic 插件来完成此操作，那就太好了。

目前，我的第一个要求是命名范围：

Trip.named_scope :residencies_empty, :conditions => ['NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id)']

第二个要求可通过 searchlogic 获得：

Trip.residences_id_equals(id)

理想的解决方案是 searchlogic 范围，如下所示：

Trip.residencies_null_or_residencies_id_equals(id)

原文

I have a trip that has many residencies. I need a single query that returns all trips where no residency information has been specified. And all trips that match a specified residency.

I can get the first from this query:

SELECT * FROM `trips` WHERE (((NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id))

But to get the second, I need to add this bit as well:

INNER JOIN `residencies` ON (`trips`.`id` = `residencies`.`trip_id`)

Adding the join before the WHERE clause is asking for results that have a residency ID and no residency IDs. That obviously returns nothing. So how can I write this to get the full result set in one query? Stored procedures aren't allowed on this.

I'm using Rails, so it's a bonus (but definitely not required) if the answer is Rails-specific. And a huge bonus if someone can show how this can be done with the searchlogic plugin.

Currently, I have the first requirement as a named scope:

Trip.named_scope :residencies_empty, :conditions => ['NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id)']

The second requirement is available through searchlogic:

Trip.residences_id_equals(id)

The ideal solution would be a searchlogic scope that looks like this:

Trip.residencies_null_or_residencies_id_equals(id)

分享到QQ

分享到微博

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

发布评论

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

人生戏 2024-08-13 01:03:33

我建议对具有特定居住地的行程使用另一个“EXIST”：

SELECT * FROM `trips` WHERE 
  (NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id))
  OR
  (EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id 
           AND other_criteria_for_specific_residency)
  )

这似乎是最具可读性的解决方案，但如果性能很重要，您应该检查 EXPAIN 以了解这是如何优化的（与 MySql 中最复杂的查询一样）。

I suggest using another "EXIST" for the trips with the specific residency:

SELECT * FROM `trips` WHERE 
  (NOT EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id))
  OR
  (EXISTS (SELECT id FROM residencies WHERE trips.id = residencies.trip_id 
           AND other_criteria_for_specific_residency)
  )

This seems to be the most readable solution, but if performance is important, you should check EXPAIN to see how this is optimized (as with most complex queries in MySql).

回复收藏 0 原文

日暮斜阳 2024-08-13 01:03:33

尝试：

SELECT * FROM `trips`
    LEFT JOIN  residencies ON trips.id = residencies.trip_id

您将从 trips 中获取所有列中的数据，但数据只会填充到存在行的驻地的列中，如果不存在驻地行，则这些列将为空。

TRY:

SELECT * FROM `trips`
    LEFT JOIN  residencies ON trips.id = residencies.trip_id

You will get data in all of the columns from trips, but data will only be populated in columns from residencies where a row existed, if no residencies row existed those columns will be null.

回复收藏 0 原文