score:3

Accepted answer

This assumes your foreign key maps Location.State to 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

The above answers your original question. After adding the Client table, I would write the query as follows:

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

Writing it with the joins explicitly makes it more readable I think.


Related Articles