Join with OR condition - 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 2012 Forums

Transact-SQL (2012)

Join with OR condition

Author

Topic

rama108
Posting Yak Master

115 Posts

Posted - 2013-10-24 : 18:00:34

I have a query with the following join, is this correct?

LEFT JOIN admin.tblUser u
ON (c.UpdatedByUserID = u.UserID
OR c.UpdatedByUserID = u.UserID)

Thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-24 : 18:43:59

quote:
Originally posted by rama108

I have a query with the following join, is this correct?

LEFT JOIN admin.tblUser u
ON (c.UpdatedByUserID = u.UserID
OR c.UpdatedByUserID = u.UserID)

Thanks.

Syntactically it appears correct. But, it doesn't make much sense.

rama108
Posting Yak Master

115 Posts

Posted - 2013-10-24 : 19:00:09

Lamprey,
Thank you for your response. Here is the full query: Does this makes sense?

SELECT c.ID,
p.FirstName + ' ' + p.LastName UploadedBy
p.FirstName + ' ' + p.LastName UpdatedBy
FROM Categories c
LEFT JOIN admin.tblUser u
ON (d.UploadedByUserID = u.UserID
OR d.UpdatedByUserID = u.UserID)
LEFT JOIN person.tblPerson p
ON u.PersonID = p.PersonID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-24 : 20:07:57

Ahh, I see. You probably miss-typed UpdatedByUserID twice in the original post. Yeah, I think that makes sense. I'm guessing the table alias of "d" should be "c". But, if the joining UserID can be in either of those two columns on table "d," then it looks correct.

rama108
Posting Yak Master

115 Posts

Posted - 2013-10-24 : 21:13:15

Thank you for verifying, Thanks for your time.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-10-25 : 11:20:38

Rama, the second query you posted has some fatal flaws (no table with a d alias, no comma after UploadedBy, no way to differentiate UploadedBy vs UpdatedBy), but I wanted to point out that
ORs in the JOIN criteria and/or WHERE clause can be a big performance killer. This is because it can prevent proper index utilization. It's going to depend on your data volume, index definition and environment, but try union:

SELECT c.ID,
'UploadedBy' AS NameType,
p.FirstName + ' ' + p.LastName AS FullName
FROM Categories c
JOIN admin.tblUser u
ON d.UploadedByUserID = u.UserID -- Where is the d alias coming from?
JOIN person.tblPerson p
ON u.PersonID = p.PersonID
UNION ALL
SELECT c.ID,
'UpdatedBy' AS NameType,
p.FirstName + ' ' + p.LastName AS FullName
FROM Categories c
JOIN admin.tblUser u
ON d.UpdatedByUserID = u.UserID -- Where is the d alias coming from?
JOIN person.tblPerson p
ON u.PersonID = p.PersonID

rama108
Posting Yak Master

115 Posts

Posted - 2013-10-25 : 21:17:50

Lazerath,
Finally I corrected the query a few hours before you posted the message. Thank you also for pointing it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources