SQL查询-表联接问题 [英] SQL Query - Table Joining Problems

问题描述

我遇到了一些严重的问题，无法解决如何针对我的情况建立适当的查询..可以肯定的是，这取决于正确地连接表，但是经过大量的谷歌搜索之后，我似乎无法弄清楚...

I am having some serious problems wrapping my head around how to build a proper query for my situation.. Pretty sure it depends on joining the tables properly but I cant seem to figure it out after a lot of googling...

我有下表.

Main:
ID, WhenDate, InfoText, StatusID, TypeID

和...

Status:
ID, StatusText    

和...

Secondary:
MainID, WhenDate

希望我能够正确解释这一点. 我在Main中有一堆记录，我需要从中获取信息.我需要能够对WhenDate和TypeID进行过滤.我还需要从链接到StatusID的状态表中获取StatusText.
问题:
辅助"表链接到Main中的记录，辅助"中的任何记录都应导致与Main中的记录相同的行，不同之处在于，使用了辅助"中的当日日期"而不是"Main"中的当日日期".

Hope Im able to explain this correctly.. I have a bunch of records in Main that I need to get info from.. I need to be able to filter on WhenDate and TypeID. I also need to get StatusText from Status table that is linked to StatusID.
The problem:
The table Secondary links to records in Main, any record in Secondary should result in a row that is identical to the record in Main with the exception that the WhenDate in Secondary is used instead of the WhenDate in Main.

任何帮助都非常感谢，即使只是一个提示，例如要使用哪种类型的联接...

Any help greatly appreciated, even if its just a hint, such as what types of joins to use or something...

推荐答案

我使用INNER JOIN作为状态，假设每个主记录都引用一个现有的状态记录.如果不是这种情况，则可能需要将其更改为LEFT JOIN.

I used INNER JOIN for status, assuming that every main record refers to an existing status record. If that is not the case, you may want to change it to a LEFT JOIN.

对于WhenDate，您只需离开即可加入Secondary.如果找到记录，则可以与Secondary.WhenDate进行比较，否则，可以与Main.WhenDate进行比较.

For the WhenDate, you can just left join Secondary. If a record is found, you can compare against Secondary.WhenDate, otherwise, check against Main.WhenDate.

SELECT
  m.ID as MainID,
  m.WhenDate as MainWhenDate, 
  m.InfoText, 
  m.StatusID,
  st.StatusText,
  m.TypeID,
  s.WhenDate as SecondaryWhenDate,
  CASE WHEN s.MainID IS NULL THEN 
    m.WhenDate 
  ELSE 
    s.WhenDate 
  END AS ActualWhenDate
FROM
  Main m
  INNER JOIN Status st ON st.ID = m.StatusID
  LEFT JOIN Secondary s ON s.MainID = m.ID
WHERE
  ( s.MainID IS NULL AND m.WhenDate = <YourDate>
    OR
    s.MainID IS NOT NULL AND s.WhenDate = <YourDate> )
  AND TypeId = <TypeFilter>
  AND ... other filters, if you need any ...

这篇关于SQL查询-表联接问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！