根据关系表从多个表中获取结果 [英] Get results from multiple tables based on relationship table

问题描述

我有 dbo.Users 表

I have dbo.Users tables

Id, Name
1, John
2, Mary
3, Michael

然后我有 dbo.Phones 表

Then I have dbo.Phones table

Id, Phonenumber
10, 1234
11, 5555

然后我有 dbo.Relationship 表

Then I have dbo.Relationship table

Id, ChildId
1, 10
2, 11

如何进行返回的查询

Id, Name, Phonenumber
1, John, 1234
2, Mary, 5555
3, Michael, NULL

这是我目前得到的.

SELECT u.Id, u.Name, p.Phonenumber
FROM dbo.Users as u
LEFT JOIN dbo.Phones as p
-- Something

SQL 小提琴

推荐答案

在此处将关系表视为用户表和电话表之间的中间人.这是一个多对多关系带有映射表.将您的用户加入关系，然后加入与您手机的关系.

Think of the Relationship table as the middle-man between your Users and Phones tables here. It is a many-to-many relationship with a mapping table. Join your Users to the Relationship and then the Relationship to your Phones.

SELECT u.Id
    ,u.Name
    ,p.PhoneNumber
FROM dbo.Users u
LEFT JOIN dbo.Relationship r ON r.Id = u.Id
LEFT JOIN dbo.Phones p ON p.Id = r.ChildId

想象一下:

用户: 你好，关系，我有 UserId = 1，我有什么 PhoneIds 对应 UserId?

Users: Hello Relationship, I have UserId = 1, what PhoneIds do I have for that UserId?

关系:您好用户.我有 PhoneId = 10 给你.我会去和 Phones 通话，看看号码是多少.

Relationship: Hi Users. I have PhoneId = 10 for you. I'll go talk to Phones to see what the number is.

电话:人际关系！我有电话号码 1234 给你.它与您给我的 PhoneId 匹配.

Phones: Hi Relationships! I have PhoneNumber 1234 for you. It matches the PhoneId you gave me.

这篇关于根据关系表从多个表中获取结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！