Join on multiple tables - 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 2005 Forums

Transact-SQL (2005)

Join on multiple tables

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-12-15 : 06:37:42

I've gotten myself in to real trouble this time....
Let's see if anyone can find a solution for this:

I have a table with transactions, which were connected to users only, but now they are connected to users AND organisations.
So when the transaction is for a user, it should give me the username and e-mail, but when it is for an organisation, it should give me the organisation name and e-mail.

the table looks like this:

Transaction
ID (int)
UserOrOrganisation (U or O)
UserID (int, holds either a userid, or organizationid)
Amount
Description
...

My sp looks like this right now:
SELECT
pt.[ID],
pt.[UserOrOrganisation],
pt.[UserID],
pt.[Amount],
pt.[Description],
u.[DisplayName],
u.[Email]
FROM
Transaction pt
Left JOIN
Users u
ON
pt.UserId = u.UserID
ORDER BY
pt.[ID]

So actually when UserOrOrganisation = "U" this will work, only when it is "O", it should be joining with the Orginisation table.
So what should I do now?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 06:44:41

I'd create a view unioning the similar columns in users and organisations together, and call it parties. I'd include a PartyType column.

Then I'd join to that.

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-12-15 : 06:47:07

I don't think that will work, since the organizationid's and userid's might be similar.
Couldn't you do a case statement or something?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-15 : 07:04:19

It will work. The PartyType column is 'U' or 'O' and is there to avoid duplicates across the 2 tables. Do the join on the PartyType and the Id (what you call UserOrOrganisation and UserID).

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-12-15 : 07:09:12

I did it with a select statement:
[DisplayName] =
(
Case pt.[UserOrOrganisation]
when 'U' then (select DisplayName from Users where Users.UserID = pt.[UserID])
when 'O' then (select OrganisationName from Organisations where OrganisationID= pt.[UserID])
End
)

The secret to creativity is knowing how to hide your sources. (Einstein)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-15 : 07:36:27

quote:
Originally posted by trouble2

I did it with a select statement:
[DisplayName] =
(
Case pt.[UserOrOrganisation]
when 'U' then (select DisplayName from Users where Users.UserID = pt.[UserID])
when 'O' then (select OrganisationName from Organisations where OrganisationID= pt.[UserID])
End
)

The secret to creativity is knowing how to hide your sources. (Einstein)

Also make sure subquery returns single value

Madhivanan

Failing to plan is Planning to fail

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2009-12-15 : 07:46:03

Yes, they are primary keys

The secret to creativity is knowing how to hide your sources. (Einstein)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources