Displaying data from 3 tables

  • jxclark

    SSC Rookie

    Points: 39

    Hello,

    I'm new to .net programming, in which I'm attempting to code a hardware inventory web app. I'm using Linq to SQL Classes.

    I have three SQL 2005 tables.

    * tblEmployee

    * tblDesktop

    * tblHardware

    In each table there's a tblID master key (auto increment). Created master/foreign keys (TblEmployee's tblID is the master key and tblDesktop, tblHardware are foreign keys).

    I'm utilizing the following code obtained from asp.net site:

    Partial Class _Default

    Inherits System.Web.UI.Page

    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

    System.Threading.Thread.Sleep(1000)

    Dim db As New AssetsDBDataContext

    Dim q = From b In db.tblEmployees _

    Where b.LastName.Contains(txtSearch.Text.Trim()) _

    And b.FirstName.Contains(txtSearch1.Text.Trim()) _

    Select b

    lv.DataSource = q

    lv.DataBind()

    End Sub

    End Class

    How it works: To see all hardware an employee is assigned with - Enter the employee's last and/or first name in the search boxes.

    I successfully get results, but I would like to optimize the search/database.

    Problem: I'm using tblID for the primary keys (auto increment) with relationships to the other tables.

    I dont want to add duplicate hardware in the tables if they already exists.

    Example: tblDesktop contains three columns - Manufacture, Model, and Serial.

    Is there away to link the tblEmployee table to the hardware tables - yielding the desired search results?

    Maybe: join tblEmployee, tblDesktop, tblLaptop to a new table (displaying all three data)?

  • Pradyothana Shastry

    SSCertifiable

    Points: 5211

    Did you checked with inner join on all 3?

  • aaronxramirez

    Valued Member

    Points: 51

    Create a view that has all the joins and fields you need then wire up your linq to that.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply