3 table query help

[kyledunn]

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

 

[mwolf00]

Select u.userName, f.firmName
FROM userTable u
JOIN manageTable m ON u.userID = m.userID
JOIN firmTable f ON m.firmID = f.firmID
WHERE u.userID = 15

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[kyledunn]

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 = 15

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 = 15

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

 

[mwolf00]

Do you have two records in the firm table with a firmID = 189? I'm guessing that you do...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[kyledunn]

I agree with you that what you say makes sense but unfortunately that is not the case. I simplified the query and it returned only three rows.

select firmID from manageTable where firmID = 189

the result is three rows with two columns

FirmID
189
189
189

also there is only one row in the userTable

select userID from userTable where userID = 'Kyle' returned only one row

UserID
Kyle

It makes sense to me as well that it is in the data but the results are conflicting. My next step is to create new sample tables, put in sample data and monitor the results. Thanks for your help.