SQL条件连接在不同的表上 [英] Sql conditional Join on different tables

问题描述

我必须在现有项目上添加一个通知模块。
我的表结构就像在图片上一样。

I've to add a notification module on an existing project. My table structure is like on the pic.

图片：

您在图片上看到的每个通知都有一个类型和一个releatedID。

as you see on the picture every notification has a type and a releatedID.

类型1 =取消预订，相关ID是预订表上的ID

Type 1 = reservation is cancelled, releated ID is the id on "reservations" table

类型3 =帐户余额低于给定的最小值。限制，所以releatedID是 account_movements
表上的ID

Type 3 = account balance is under the given min. limit so releatedID is the id on "account_movements" table

我要执行的操作是有条件的连接，以避免2个不同的sql查询；

what I'm trying to do is a conditional join to avoid 2 different sql queries;

1. 获取属于该人的所有通知


2. 从基于 notification的不同表中获取通知详细信息.Type

所以问题是我可以在一个查询中做到吗？

So the question is can I do it in one query ?

推荐答案

类似以下的方法应该起作用。基本上，您可以指定通知表中的哪些记录与预订表或中的记录联接当您加入这些帐户时，使用account_movements 表。使用LEFT JOIN，以便所有您的 notification 记录都能通过，并且仅预订表或具有匹配项的 account_movements 可以通过。

Something like the following should work. Basically you specify which records in your notifications table join with records in your reservations table or your account_movements table when you join those in. Use a LEFT JOIN so that ALL of your notification records make it through, and only those records in the reservations table OR the account_movements that have a match make it through.

SELECT 
  n.id,
  n.type,
  n.companyid,
  n.personid,
  n.relatedid,
  n.description,
  r.details as reservation_details,
  am.details as account_movement_details,
  COALESCE(r.details, am.details) AS combined_detail
FROM
  notifications n
  LEFT OUTER JOIN reservations r ON
    n.relatedid = r.id AND
    n.type = 1
  LEFT OUTER JOIN account_movements am ON
    n.relatedid = am.id AND
    n.type = 3

此处是 SQL FIDDLE 以及解决方案。

Here is a SQL FIDDLE with the solution as well.

我在 COALESCE（）中添加了代码，只是为了表明由于JOINS是互斥的，因此可以安全地合并您的预订表和 account_movements 表中的所有列合并为一个列，而不必担心丢失或重复任何数据。

I added in the COALESCE() just to show that since the JOINS are mutually exclusive, you can safely combine columns from your reservations table and your account_movements table into a single column without fear of missing or duplicating any data.

这篇关于SQL条件连接在不同的表上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！