none
joining three tables using linq RRS feed

  • Question

  • Hello,

     

    I have been trying to join three tables together but cant get the correct output. I am using VB express 2008. Here is what I am trying to do;

     

    I have 3 tables and the following fields;

     

    Customers: cust_id, name

    Orders: order_id, cust_id, orderno

    Order details: or_detail_id, order_id, cust_id, product

     

    what I want to do is;

     

    cust           orders      details

    A               101          camera

                                     battery

                     102          bag

    B               103          camcorder

                                     battery

                     104          cds

                     105          cable

      book

     

    I can do it for two tables;

     

    Dim alldata = From d1 In D1DataSet2.customers _

    Group Join d2 In D1DataSet2.orders On d2.customer_id Equals d1.customer_id _

    Into list1 = Group _

    Select d1, list1

     

    Dim saveFileDialog1 As New SaveFileDialog()

    Dim values As String

     

    saveFileDialog1.RestoreDirectory = True

    If saveFileDialog1.ShowDialog() = DialogResult.OK Then

     

     

    values = saveFileDialog1.FileName

    For Each d1 In alldata

    My.Computer.FileSystem.WriteAllText(values, d1.customers.name & ControlChars.NewLine, True)

    For Each g1 In d1.list1

    My.Computer.FileSystem.WriteAllText(values, g1.orderno & ControlChars.NewLine, True)

    Next

    Next

     

    end if

     

    How can I include the order details table and get the result above? Any help will be greatly appreciated. Thank you all...
    Thursday, August 14, 2008 12:15 PM

Answers

  • I think I got what I want by just doing this;

     

    Dim db1 = From a In D1DataSet2.customers

    For Each row1 In db1

    My.Computer.FileSystem.WriteAllText(values, row1.name & ControlChars.NewLine, True)

    Dim c_id As String = row1.customer_id

    Dim db2 = From b In D1DataSet2.orders Where b.customer_id = c_id

    For Each row2 In db2

    My.Computer.FileSystem.WriteAllText(values, row2.orderno & ControlChars.NewLine, True)

    Dim c_id2 As String = row2.customer_id

    Dim or_id As String = row2.order_id

    Dim db3 = From c In D1DataSet2.d3 Where c.customer_id = c_id2 And c.order_id = or_id

    For Each row3 In db3

    My.Computer.FileSystem.WriteAllText(values, row3.product & ControlChars.NewLine, True)

    Next

    Next

    Next

     

    thank you all anyway

    Thursday, August 14, 2008 1:30 PM

All replies

  • I think I got what I want by just doing this;

     

    Dim db1 = From a In D1DataSet2.customers

    For Each row1 In db1

    My.Computer.FileSystem.WriteAllText(values, row1.name & ControlChars.NewLine, True)

    Dim c_id As String = row1.customer_id

    Dim db2 = From b In D1DataSet2.orders Where b.customer_id = c_id

    For Each row2 In db2

    My.Computer.FileSystem.WriteAllText(values, row2.orderno & ControlChars.NewLine, True)

    Dim c_id2 As String = row2.customer_id

    Dim or_id As String = row2.order_id

    Dim db3 = From c In D1DataSet2.d3 Where c.customer_id = c_id2 And c.order_id = or_id

    For Each row3 In db3

    My.Computer.FileSystem.WriteAllText(values, row3.product & ControlChars.NewLine, True)

    Next

    Next

    Next

     

    thank you all anyway

    Thursday, August 14, 2008 1:30 PM
  • are you searching for this example ,may be this post helpfull for you..

    http://professionals-helpdesk.blogspot.com/2012/04/joining-three-table-entities-using-linq.html

    Thursday, April 19, 2012 11:03 AM