SQL Join Query - 2 Foreign Keys Relating to 1 Key

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

General SQL Server Forums

New to SQL Server Programming

SQL Join Query - 2 Foreign Keys Relating to 1 Key

Author

Topic

hurdy
Starting Member

17 Posts

Posted - 2010-02-09 : 16:53:49

Hi everyone,

Thank you for the time taken out the read this. I'm having some difficulty creating an SQL Query. Let me explain...

I have 2 tables UserAccounts and Faults

The Faults table contains 3 Colums.
FaultID RecordedByID CompletedByID

The UserAccounts table contains 2 columns.
UserID FullName

Stored in the RecordedByID and CompletedByID columns in the Faults table are values that reference to users, the UserID columns in the UserAccounts table.

How can I make 1 SQL Query that can pull multiple names from 1 table that relate to the values in the other?

e.g.
Table UserAccounts Contains:

UserID FullName
1 John Smith
2 Robert Jones
3 Kevin Watkins

Table Faults contains:
FaultID RecordedByID CompletedByID
1 2 3
2 1 1
3 1 2
4 3 1

I want a query that will output the following:

FaultID: RecordedBy: CompletedBy
1 Robert Jones Kevin Watkins
2 John Smith John Smith
3 John Smith Robert Jones
4 Kevin Watkins John Smith

Can anyone help please?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-02-09 : 17:06:22

Comething like this?

SELECT
	F.FaultID,
	UA1.Fullname AS RecordedBy,
	UA2.FullName AS CompletedBy
FROM
	Faults AS F
INNER JOIN
	UserAccounts AS UA1
	ON F.RecordedByID = UA1.UserID
INNER JOIN
	UserAccounts AS UA2
	ON F.CompletedByID = UA2.UserID

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 17:06:33

[code]
SELECT FaultID, RB.FullName AS RecordedBy, CB.FullName AS CompletedBy
FROM Faults AS F
JOIN UserAccounts AS RB
ON RB.UserID = F.RecordedByID
JOIN UserAccounts AS CB
ON CB.UserID = F.CompletedByID
ORDER BY F.FaultID
[/code]

Kristen
Test

22859 Posts

Posted - 2010-02-09 : 17:12:24

Darn it!

hurdy
Starting Member

17 Posts

Posted - 2010-02-09 : 17:44:52

Excellent! Thank you very very much both. It works perfectly!

I appeciate your prompt assistance.

Rob

hurdy
Starting Member

17 Posts

Posted - 2010-02-09 : 19:00:15

I have found one problem with this. Because the database accepts NULL values, the query is not pulling all of the data in the table because some values are null in the CompletedByID column.

Are there any suggestions about how to fix this so that the query will return the row even if one of the ID columns contain a NULL?

I guess this is happening because the QUERY is trying to reference an item in the CompletedByID column which is NULL to an item in the "Look Up" table.

hurdy
Starting Member

17 Posts

Posted - 2010-02-09 : 19:35:05

Ok I've solved this.
I used a LEFT OUTER JOIN statement instead.

Thank you for your previous help, it is greatly appreciated.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-02-10 : 04:21:44

See my solution :

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139064

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources