Displaying data from 3 tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Service Packs » Displaying data from 3 tables

Displaying data from 3 tables

Rate Topic

Display Mode

Topic Options

Author

Message

jxclark

Posted Tuesday, May 19, 2009 2:17 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, May 21, 2009 3:02 PM
Points: 1, Visits: 2

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)?

Post #720153

Pradyothana Shastry

Posted Thursday, May 21, 2009 1:40 PM

SSCommitted

Group: General Forum Members
Last Login: Monday, March 25, 2013 5:30 AM
Points: 1,641, Visits: 423

Did you checked with inner join on all 3?

- Pradyothana

http://www.msqlserver.com

Post #721660

aaronxramirez

Posted Friday, May 22, 2009 12:53 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 21, 2012 2:50 PM
Points: 9, Visits: 15

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

Post #722338

« Prev Topic | Next Topic »

Permissions