Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Displaying data from 3 tables Expand / Collapse
Author
Message
Posted Tuesday, May 19, 2009 2:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Thursday, May 21, 2009 1:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 5, 2014 9:54 PM
Points: 1,683, Visits: 451
Did you checked with inner join on all 3?



- Pradyothana


http://www.msqlserver.com
Post #721660
Posted Friday, May 22, 2009 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse