Frustrating multiple table joins

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

Frustrating multiple table joins

Author

Topic

dvongrad
Starting Member

6 Posts

Posted - 2010-03-03 : 20:11:15

Since this is my first time here, please accept my apologies and direct me to the proper forum if this has been posted in an incorrect area.

I have 5 tables which I need to join in order to retrieve some data. For the purposes of this post, these tables are defined as follows, with the first item being the primary key:

Client: ClientId, FirstName, LastName, Active
ClientAddress: ClientAddressId, ClientId, AddressId
Address: AddressId, StreetAddress
ClientMortgage: ClientMortgageId, ClientId, MortgageId
Mortgage: MortgageId, TermStartDate, TermEndDate

Because this schema was migrated from very defunct Access data in 1 flat table, a client may not necessarily have an address and may not even have a mortgage, but could have multiples of both.

When I construct the following query:

SELECT
c.ClientId, c.LastName, c.FirstName, a.StreetAddress,
m.MortgageID, m.TermStartDate, m.TermEndDate
FROM JB.dbo.Client c
LEFT JOIN JB.dbo.ClientAddress ca ON c.ClientID = ca.ClientId
LEFT JOIN JB.dbo.Address a ON a.AddressId = ca.AddressId
LEFT JOIN JB.dbo.ClientMortgage cm ON cm.ClientId = c.ClientId
LEFT JOIN JB.dbo.Mortgage m ON m.MortgageId = cm.MortgageId
WHERE c.Active = 1
AND c.ClientID = 29

I'm getting 4 rows return when this particular client has only 2 addresses and 2 mortgages. I am getting a Cartesian product on the query because of the joins, which looks like (data changed for confidentiality only):

ClientId LastName FirstName StreetAddress MortgageID TermStartDate TermEndDate
29 Smith John 123 MAIN 35 NULL NULL
29 Smith John 123 MAIN 36 2010-01-22 00:00:00.000 NULL
29 Smith John 124 MAIN 35 NULL NULL
29 Smith John 124 MAIN 36 2010-01-22 00:00:00.000 NULL

What I should be getting is 2 rows where MortgageId 35 has address 123 MAIN and MortgageId 36 has address 124 MAIN.

So the question is, how do I construct this query using SQL Server 2008 Express such that only the 2 rows that I expect are returned? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 01:31:56

what are the two rows you expect to return out of above 4?

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

dvongrad
Starting Member

6 Posts

Posted - 2010-03-04 : 10:47:37

I'm expecting back rows 1 and 4. Sorry for not making that clear in my original post.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 10:49:47

on what basis you saw 1 and 4? how are those tables related other than ClientId?

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-04 : 11:24:16

The point is, how do you say, mortgage id 35 needs address " 123 MAIN " and mortgage id 36 needs address " 124 MAIN ". Why not other way....thats why you have 4 rows.

dvongrad
Starting Member

6 Posts

Posted - 2010-03-04 : 11:52:46

ClientAddress is the link between Client and Address and ClientMortgage is the link between Client and Mortgage. Both these link tables have foreign keys to each of their "parent" tables as I mentioned in my original post.

I know rows 1 and 4 should be the rows returned because I hand checked the data in all five tables that are involved. Just to ensure that there wasn't an issue in converting the flat Access table to SQL Server through my own .NET export application, I checked the Access DB as well. All the data in the SQL Server schema is correct because all cross-referencing of the data in both the old Access schema and new SQL Server schema has proven that:

MortgageId 35 has an address of 123 MAIN and both TermStartDate and TermEndDate are NULL
MortgageId 36 has an address of 124 MAIN and TermStartDate IS NOT NULL and TermEndDate IS NULL

I'm not sure if this answers your question, so let me know if you need further clarification.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 11:55:30

partly answers the question. the part remaining is how you connect them to Client table? is relation through only clientid?

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

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-04 : 12:09:31

Then it appears Motrgage table also has an Address id?
Looks at this sample data.

declare @client table(clientid int, lname varchar(20))
insert @client
select 1,'vijay'

declare @address table (clientid int,addressid int, street varchar(200))
insert @address
select 1,1,'123 main st.'
union all select 1,2,'124 main st.'

declare @mortgage table (clientid int, mortgageid int, addressid int,termstartdate datetime)
insert @mortgage
select 1,35,1,null
union all select 1,36,2,'20090101'

Is this how your actual data looks like?
Run the query once..and then uncomment the last line and run this again..you will know the difference

select a.clientid,a.lname,b.street,c.mortgageid,c.termstartdate
from @client a
left join @address b on a.clientid = b.clientid
left join @mortgage c on a.clientid = c.clientid 
--and c.addressid = b.addressid

dvongrad
Starting Member

6 Posts

Posted - 2010-03-04 : 12:22:45

If I'm understanding your correctly, ClientId is ultimately the column that ties everything together. Since a client may not necessarily have an address, then

SELECT c.ClientID, c.FirstName, c.LastName, a.StreetAddress
FROM JB.dbo.Client c
LEFT JOIN JB.dbo.ClientAddress ca ON c.ClientId = ca.ClientId
LEFT JOIN JB.dbo.Address a on a.AddressId = ca.AddressId
WHERE c.Active = 1 AND c.ClientId = 29

brings back 2 rows for Client 29 (John Smith) with 2 addresses. The query (where the client may not have a mortgage)

SELECT m.MortgageId, m.TermStartDate, m.TermEndDate
FROM JB.dbo.Mortgage m
LEFT JOIN JB.dbo.ClientMortgage cm ON cm.MortgageId = m.MortgageId
WHERE cm.ClientId = 29

brings back 2 rows where the the 2 dates for MortgageId 35 are NULL and only the TermEndDate for MortgageId 36 is NULL.

As I mentioned before, these results are correct. But now I need to combine the queries to get back only rows 1 and 4 as I described above.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:27:50

for that you need an extra condition to relate to. using only clientid to link causes them to match each other and give you 2 * 2 = 4 rows which is what you're getting

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

dvongrad
Starting Member

6 Posts

Posted - 2010-03-04 : 12:34:58

You are correct in saying that Mortgage also has an AddressId, but this Address could be something completely different again. For example, the client could live at and have a mortgage on 123 MAIN, also have an adress of 124 MAIN that has no mortgage attached to it, but still have another mortgage on 125 MAIN.

I think a large part of my problem is having inherited a SQL Server schema designed by a DBA that I have no real say in how it should look like, my limited experience in joining more than three tables and not fully understanding if I should put the "AND" conditions as part of the JOINs or in the WHERE clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-04 : 12:37:27

sorry without understanding correct we cant suggest anything as it will only result in giving you wrong/meaningless result

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

dvongrad
Starting Member

6 Posts

Posted - 2010-03-04 : 12:56:49

Regarding the post from vijayisonly with the query:

select a.clientid,a.lname,b.street,c.mortgageid,c.termstartdate
from @client a
left join @address b on a.clientid = b.clientid
left join @mortgage c on a.clientid = c.clientid
--and c.addressid = b.addressid

mortgage c does not have a clientid. Client and Mortgage and linked via ClientMortgage that has a ClientId and MortgageId. Thus I still need another JOIN to tie Client and Mortgage via ClientMortgage.

Regarding the last two posts from visakh16:

I know I need another condition but I'm not sure what that should be. I also understand that trying to help is difficult without seeing the full schema, but I'm not sure what other info I can provide that will further clarify things other than the basic definition of the five tables involved and the relationships between them. I wonder if subqueries or unions may help but I'll have to look further into that by trying out what vijayisonly suggested.

I genuinely appreciate everyone's effort in trying to help me resolve this issue. That's why I value these forums so much. If you have any further ideas, please don't hesitate to post them and if I come across anything, I'll be sure to post my solution as well. Thanks again.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-04 : 13:13:40

quote:
mortgage c does not have a clientid. Client and Mortgage and linked via ClientMortgage that has a ClientId and MortgageId. Thus I still need another JOIN to tie Client and Mortgage via ClientMortgage

Yes, I see that form your OP. but I was referring to the sample data that I have provided. I have included both ClientId and MortgageId within @mortgage table.

quote:
I know I need another condition but I'm not sure what that should be.

How about you post table structure and Sample data from these tables. That will clear some things up.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources