SQL Join

[gonzalioz]

Hi all,

Let's say I have two tables:

tblComputer
computername
description

tblUser
username
password

A computer can have multiple users.
A user can only have 1 computer.

How would a table look like if there were 3 users using 1 computer and I joined them?

Like this?

tblJoined
tblComputer.computername
tblComputer.description
tblUser.username
tblUser.password
tblUser.username
tblUser.password
tblUser.username
tblUser.password

[abhijit]

I think this would be better demonstrated with sample data.

How do you plan to join tblUser with tblComputer? I don't see a common key.

[WillieMakit]

You will need to restructure your tables a little, like this:

tblComputer
tblComputerID (identity field, Primary Key)
computername
description

tblUser
tblComputerID (Foreign Key, related to tblComputer/tblComputerID)
username
password

The tblUser/tblComputerID points to which computer is associated with which user.

Then your SQL Statements will have to contain a JOIN on the key

Make sense?

Regards,

Will

[BackWoodsCoder]

SELECT c.[Name],c.Description, u.UserName, u.PassWord
FROM [User] u
    INNER JOIN Computer c on u.computerID = c.ID

Will produce a result set like so:
Name Description UName Password
Comp1 Development Machine JCG 1234
Comp1 Development Machine MPH aaabbb
Comp1 Development Machine DRH Zebra