Select with join to two referenced tables

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

Select with join to two referenced tables

Author

Topic

Chipper
Starting Member

4 Posts

Posted - 2011-08-18 : 13:15:03

I have a table that shows testing information for a certain group of people. There are two types of people who can take the test: one is a group of people who have registered and have been verified and the other is a group that will never register but takes the test on invitation. These groups must remain separate for business reasons and the data gathered on each are in separate tables. (I inhertited that part of the database and am not authorized to change it). The test table contains the following columns:

test_id - identifier
test_date - date of test
tested_id - id of person taking the test
id_source - which group table the tested_id refers to (Value 10 = registered, 99 = invited)
completed - boolean to show whether the test was completed or not
score - score of the test

In the "registered" table I have, among other data elements:

person_id
last_name
first_name
organization

In the "invited" table I have the same data elements (and others as well)

Yes, I know - not so good table design but what can you do when you inherit this stuff?

For example I have in the table the following data:

10 08/11/2011 438 10 1 100 (this person is registered and has completed a test with a score of 100)
20 08/11/2011 1712 10 0 NULL (this person is registered and has not completed the test - no score yet)
30 08/12/2011 813 99 1 86 (this person was invited and has completed a test with a score of 86)
40 08/21/2011 438 99 1 72 (this person was invited and has completed a test with a score of 72)

Note that in the data above I have two persons wit identical tested_ids but differentiated by the id_source.

Now, I want a report of all people who have tested. I need:

Last Name
First Name
Organization
Date Tested
Score

The first three elements need to come from the referenced tested_id table and the last two from the test table.

I am just beginning to learn TSQL syntax and could use some help constructing a select statement to do the above.

Thank you in advance for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 13:32:58

seems to be a simple join like below


SELECT p.last_name,
p.first_name,
p.organization,
t.dateTested,
t.Score
FROM test t
INNER JOIN (
SELECT
person_id,
last_name,
first_name,
organization
FROM registered
UNION ALL
SELECT person_id,
last_name,
first_name,
organization
FROM invited)p
ON p.person_id = t.tested_id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chipper
Starting Member

4 Posts

Posted - 2011-08-18 : 17:45:02

Thanks visakh 16. This is close but the Select query as you describe returns two entries when the same person_id is in both the registered and invited tables. In other words, from the example give, test_id 10 would have a line for the first_name, last_name and organization for from the person whose id was 438 in the registered table and another line for the first_name, last_name and organization for the person whose person_id happened to be 438 in the invited table - and these are two different people.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 01:58:56

thats fine but will that make a difference? so far as name and other details are different its fine rite? Or do you want to restrict only one person per test?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chipper
Starting Member

4 Posts

Posted - 2011-08-19 : 10:06:32

Yes, it makes a difference. For the sample data here:

test table
10 08/11/2011 438 10 1 100
20 08/11/2011 1712 10 0 NULL
30 08/12/2011 813 99 1 86
40 08/21/2011 438 99 1 72

registered table
438 Smith George MyCorp
511 Jones Bob Microswift Co
1712 Thumb Tom Terrific Toys

invited table
119 White Barbara OfficePlus
438 Black Karen USB TV
813 Green Hank Colors, Inc

What I want to see as results:
Smith George MyCorp 08/11/2011 100
Thumb Tom Terrific Toys 08/11/2011 NULL
Green Hank Colors, Inc 08/12/2011 86
Black Karen USB TV 08/21/2011 72

What your query returns
Smith George MyCorp 08/11/2011 100
Black Karen USB TV 08/11/2011 100
Thumb Tom Terrific Toys 08/11/2011 NULL
Green Hank Colors, Inc 08/12/2011 86
Black Karen USB TV 08/21/2011 72
Smith George MyCorp 08/21/2011 72

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 10:55:53

[code]
SELECT p.last_name,
p.first_name,
p.organization,
t.dateTested,
t.Score
FROM test t
INNER JOIN (
SELECT
person_id,
last_name,
first_name,
organization,
10 AS Source
FROM registered
UNION ALL
SELECT person_id,
last_name,
first_name,
organization,
99
FROM invited)p
ON p.person_id = t.tested_id
AND p.source = t.Id_source
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Chipper
Starting Member

4 Posts

Posted - 2011-08-19 : 13:59:53

Thank you! That did the trick for me. But I also want to learn from this (what else is this forum for, after all?). I follow everything in your code except the "99". Why is that there?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:41:41

i have put it as a filler for id_source field in your source table to enable joining to correct record from either invited/registered table since ids can repeat in both

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources