CODEWITHSUNDEEP
sqldatabasevb.netlinq
Landmine
Landmine

Reputation: 1789

How can I get my information from the 2 tables into 1 row?

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

That's what I have and it is very wrong. Everything shows up, but the data displays incorrectly, displays more rows than exist.

enter image description here

I think I may have it, but could you look at my code and tell me if you see any issues?

        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

Upvotes: 2

Views: 77

Answers (1)

Steven Schroeder
Steven Schroeder

Reputation: 6194

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.

Upvotes: 3

Related Questions