3 table query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

3 table query help

Author

Topic

kyledunn
Starting Member

7 Posts

Posted - 2003-05-16 : 12:50:20

I have a User table with:

UserID
UserName

I have a Manage table with:

UserID
FirmNumber

and I have a Firm table with:

FirmID
FirmName

If I know the user's ID, what query would I use to return a table with two columns, UserName and FirmName for only the rows that the UserID in the User table match the UserID in the Manage table?

Thank you for helping.

Kyle

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-05-16 : 13:02:30


select
    u.username,
    f.firmname
from
    dbo.users u
    inner join dbo.manage m
        on u.userid = m.userid
    inner join dbo.firm f
        on m.firmnumber = f.firmid
where
    u.userid = @userid

Jay White
{0}

kyledunn
Starting Member

7 Posts

Posted - 2003-05-16 : 15:31:49

Thank you very much for the help. I still don't get the results I expect. Here is a further breakdown in case you might be able to point out where I'm going wrong.

If I select user name and firm id from the user table and the manage table with:

select u.userName, m.firmID from userTable u
join manageTable m on u.userID = m.userID
where u.userID = @userID

the result is three rows with two columns

UserID FirmID
Kyle 189
Kyle 199
Kyle 259

when I add the firm name using the second join to pull the firm name:

select u.userName, m.firmID, f.firmName from userTable u
join manageTable m on u.userID = m.userID
join firmTable f on m.firmID = f.firmID
where u.userID = @userID

I get these results:

UserID FirmID FirmName
Kyle 189
Kyle 189 TheFirmName
Kyle 199 TheFirmName
Kyle 259 TheFirmName

In the database the firm name "TheFirmName" is the same for all three records. This is the actual data and is correct. What I can't figure out is where the extra record with the blank firm name and the duplicated firm ID is coming from. There are only three records in the manage table that match the user table but when I join the firm name I get the additional record. I just want to add the firm name to those three records but as written I always get this extra record with and duplicate firm number and a blank firm name returned. Any ideas?

Thanks again,

Kyle

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-16 : 15:41:58

I'd guess that you have a duplicate firm id in the firm table with a blank firm name.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources