sql - 如何将我的信息从 2 个表中获取到 1 行中？

Question

Dim LocationList =
From rows In db.Locations, clientRow In db.Clients, stateData In db.Ref_States
Where rows.State = stateData.ID
Select ClientName = clientRow.Name, rows.ID, rows.Address1, rows.Address2, rows.City, StateName = stateData.Name, ShortStateName = stateData.ShortName, rows.ZipCode Order By Address1

这就是我所拥有的，这是非常错误的。一切都显示出来了，但数据显示不正确，显示的行比现有的多。

我想我可能有它，但你能看看我的代码，如果你发现任何问题，请告诉我吗？

        Dim LocationList =
        From rows In db.Locations, clientRow In db.Clients, stateData In db.Ref_States
        Where rows.State = stateData.ID And rows.ClientID = ucClientID And rows.ClientID = clientRow.ID
        Select ClientName = clientRow.Name, rows.ID, rows.Address1, rows.Address2, rows.City, StateName = stateData.Name, ShortStateName = stateData.ShortName, rows.ZipCode Order By Address1

Answer 1

这假设您的外键将 Location.State 映射到 Ref_State.ID

Dim LocationList = From rows In db.Locations _
                   Join rs In Ref_State On rows.State Equals rs.ID _
                   Order By rows.Address1 _
                   Select rs.Name

以上内容回答了您最初的问题。添加 Client 表后，我将按如下方式编写查询:

Dim LocationList = _
    From clientRow In db.Clients _
    Join rows in db.Locations On clientRow.ID Equals rows.ClientID _
    Join db.stateData In Ref_States On rows.State Equals stateData.ID _ 
    Select ClientName = clientRow.Name, rows.ID, rows.Address1, rows.Address2, rows.City, StateName = stateData.Name, ShortStateName = stateData.ShortName, rows.ZipCode _
    Order By Address1

我认为用连接明确地编写它会使其更具可读性。