Simple Join Problem - 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

SQL Server Development (2000)

Simple Join Problem

Author

Topic

jpwalters
Starting Member

3 Posts

Posted - 2007-12-26 : 17:34:48

Problem: I have 3 tables that I'm joining together. I think the statement should return 4 rows but it only returns 3 rows.

Table 1 = tblOrder {OrderID}
Table 2 = tblType1 {Type1ID,OrderID}
Table 3 = tblType2 {Type2ID,OrderID}

SQL Statment:

SELECT tblOrder.OrderPK, tblType1.Type1ID, tblType2.Type2ID
FROM tblOrder INNER JOIN
tblType1 ON tblOrder.OrderID = tblType1.OrderID INNER JOIN
tblType2 ON tblOrder.OrderID = tblType2.OrderID
WHERE (tblOrder.OrderID = 1)

Note: There is 1 row in tblOrder table, 1 row in tblType1 table and 3 rows in tblType2 table.

Current Results:

OrderID Type1ID Type2ID
1 1 1
1 1 2
1 1 3

Desired Results:

OrderID Type1ID Type2ID
1 Null 1
1 Null 2
1 Null 3
1 1 Null

Can anyone tell me the correct SQL statement in order to produce my desired results?

Thanks so much.

-James

singularity
Posting Yak Master

153 Posts

Posted - 2007-12-26 : 18:59:36

SELECT tblOrder.OrderPK, tblType1.Type1ID, tblType2.Type2ID
FROM tblOrder LEFT JOIN
tblType1 ON tblOrder.OrderID = tblType1.OrderID LEFT JOIN
tblType2 ON tblOrder.OrderID = tblType2.OrderID
WHERE (tblOrder.OrderID = 1)

jpwalters
Starting Member

3 Posts

Posted - 2007-12-26 : 19:39:17

Thanks singularity for the quick reply.

I tried your solution and it did NOT work.

For every record where there is a row in the Type1 table and a row in the Type2 table the results show in a single row record instead of two row records.

Regardless if I uses your SQL statement or mine I still get the following results. Three records are returned instead of four.

Current Results:

OrderID Type1ID Type2ID
1 1 1
1 1 2
1 1 3

Any more ideas?

-James

singularity
Posting Yak Master

153 Posts

Posted - 2007-12-26 : 23:19:00

My bad. Try this:

SELECT tblOrder.OrderID, a.Type1ID, a.Type2ID
FROM tblOrder INNER JOIN (SELECT OrderID, Type1ID, NULL AS Type2ID FROM tblType1 UNION SELECT OrderID, NULL AS Type1ID, Type2ID from tblType2) a ON tblOrder.OrderID = a.OrderID

jpwalters
Starting Member

3 Posts

Posted - 2007-12-27 : 02:06:24

That worked perfectly!

I'm now adapting this to my real query which is fare more complex. I I may have to post one other question but if not I really really appreciate your help!

-James

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources