SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Join with OR condition
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rama108
Posting Yak Master

102 Posts

Posted - 10/24/2013 :  18:00:34  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 10/24/2013 :  18:43:59  Show Profile  Reply with Quote
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.
Go to Top of Page

rama108
Posting Yak Master

102 Posts

Posted - 10/24/2013 :  19:00:09  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 10/24/2013 :  20:07:57  Show Profile  Reply with Quote
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.
Go to Top of Page

rama108
Posting Yak Master

102 Posts

Posted - 10/24/2013 :  21:13:15  Show Profile  Reply with Quote
Thank you for verifying, Thanks for your time.
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
319 Posts

Posted - 10/25/2013 :  11:20:38  Show Profile  Reply with Quote
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
Go to Top of Page

rama108
Posting Yak Master

102 Posts

Posted - 10/25/2013 :  21:17:50  Show Profile  Reply with Quote
Lazerath,
Finally I corrected the query a few hours before you posted the message. Thank you also for pointing it out.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000