代码之家 › 专栏 › 技术社区 › Matt Oestreich

基于单独查询结果连接多个表的SQL查询

sql-server sql c#

Matt Oestreich · 技术社区 · 7 年前

我正在试图找到正确的SQL查询,以便基于单独的查询将多个表连接在一起。。

然后,我需要使用这些“硬件ID”和“软件ID”来查询“硬件”和“软件”表,以获取特定用户需要的硬件和软件的友好名称。

我已经尝试过子查询/交叉连接和我在这个网站上找到的许多其他查询-似乎没有一个能满足我的需要。。任何建议都绝对令人惊讶!

4 回复 | 直到 7 年前

Steve 7 年前

它应该只是一个简单的多级连接

SELECT TitleName, HardwareName, SoftwareName
FROM Titles
INNER JOIN Titles_Hardware
ON Titles.TitleId = Titles_Hardware.TitleId
INNER JOIN Titles_Software
ON Titles.TitleId = Titles_Software.TitleId
INNER JOIN Hardware
ON Hardware.HardwareId = Titles_Hardware.HardwareId
INNER JOIN Software
ON Software.SoftwareId = Titles_Software.SoftwareId
WHERE Titles.TitleId = {something}

GMB 7 年前

您应该能够使用一系列内部联接来实现您的目标,如:

select 
    t.titleName,
    h.hardwareName,
    s.softwareName
from titles t
inner join titles_hardware th on th.titleId = t.titleId
inner join hardware h on h.hardwareId = th.hardwareId
inner join titles_sotfware ts on ts.titleId = t.titleId
inner join software s on s.softwareId = ts.softwareId
where t.titleId = 'foo'

Drunken Code Monkey 7 年前

SELECT t.TitleName, t.TitleId, h.HardwareName, s.SoftwareName
FROM Titles t
INNER JOIN Title_Hardware th ON th.TitleId = t.TitleId
INNER JOIN Hardware h ON h.HardwareId = th.HardwareId
INNER JOIN Title_Software ts ON ts.TitleId = t.TitleId
INNER JOIN Software s ON s.SoftwareId = ts.SoftwareId
WHERE t.TitleId = 'bleh'

编辑

从评论中,您需要三个查询来获得所需内容:

SELECT t.TitleName
FROM Titles t
WHERE t.TitleId = 'bleh'

SELECT h.HardwareName
FROM Titles t
INNER JOIN Title_Hardware th ON th.TitleId = t.TitleId
INNER JOIN Hardware h ON h.HardwareId = th.HardwareId
WHERE t.TitleId = 'bleh'

SELECT s.SoftwareName
FROM Titles t
INNER JOIN Title_Software ts ON ts.TitleId = t.TitleId
INNER JOIN Software s ON s.SoftwareId = ts.SoftwareId
WHERE t.TitleId = 'bleh'

LukStorms 7 年前

DECLARE @TitleName VARCHAR(100) = 'The Boss Lady';

SELECT hard.HardwareName AS Name, 'hard' as TitleType, tit.TitleName
FROM Titles tit
JOIN Titles_Hardware tithard ON tithard.TitleId = tit.TitleId
JOIN Hardware hard ON hard.HardwareId = tithard.HardwareId
WHERE tit.TitleName = @TitleName

UNION ALL

SELECT soft.SoftwareName, 'soft', tit.TitleName
FROM Titles tit
JOIN Titles_Software titsoft ON titsoft.TitleId = tit.TitleId
JOIN Software soft ON soft.SoftwareId = titsoft.SoftwareId
WHERE tit.TitleName = @TitleName

推荐文章