SQL View Help with Table Joins

Here is my question?
I have the following database objects that i need to use for illustrating my question

3 SQL Tables
  USER_REGISTRATION UR
  TEACHER_REGISTRATION URR
  DOMAIN_WEBMAIL  WM

The WM contains columns that i need to use to construct my view.
FROM_CONTACT_ID
TO_CONTACT_ID
These column store integer values and based on the value these column hold, i need to join them with the either the UR or URR table. Now the question, i know how to join them but i wish to know if i could set a criteria of which table to join with based on the value of another column of WM.

ie  if column RECIPIENT_CATEGORY_ID hold 1, then join with UR otherwise join with URR

Can this be achieved. If yes, please show me how using my sql script below.



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DOMAIN_WEBMAIL_VIEW]
AS
SELECT     
	WM.ID, 
	ISNULL(WM.FROM_CONTACT_ID,0) AS FROM_CONTACT_ID,
	ISNULL(UR.FULL_NAME,'') AS FROM_CONTACT,
	ISNULL(WM.RECIPIENT_CATEGORY_ID,0) AS RECIPIENT_CATEGORY_ID,
	ISNULL(WM.TO_CONTACT_ID,0) AS TO_CONTACT_ID,
	ISNULL(URR.FULL_NAME,'') AS TO_CONTACT,
	ISNULL(WM.MAIL_SUBJECT, '') AS MAIL_SUBJECT,
	ISNULL(WM.MAIL_BODY, '') AS MAIL_BODY,
	
 FROM         
dbo.DOMAIN_WEBMAIL AS WM LEFT OUTER JOIN
dbo.USER_REGISTRATION AS UR ON WM.TO_CONTACT_ID = UR.ID LEFT OUTER JOIN
dbo.TEACHER_REGISTRATION AS URR ON WM.FROM_CONTACT_ID = URR.ID

Open in new window

TECH_NETAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JestersGrindCommented:
You could do something like this.

Greg



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DOMAIN_WEBMAIL_VIEW]
AS
SELECT     
        WM.ID, 
        ISNULL(WM.FROM_CONTACT_ID,0) AS FROM_CONTACT_ID,
        ISNULL(UR.FULL_NAME,'') AS FROM_CONTACT,
        ISNULL(WM.RECIPIENT_CATEGORY_ID,0) AS RECIPIENT_CATEGORY_ID,
        ISNULL(WM.TO_CONTACT_ID,0) AS TO_CONTACT_ID,
        ISNULL(URR.FULL_NAME,'') AS TO_CONTACT,
        ISNULL(WM.MAIL_SUBJECT, '') AS MAIL_SUBJECT,
        ISNULL(WM.MAIL_BODY, '') AS MAIL_BODY,
        
 FROM         
dbo.DOMAIN_WEBMAIL AS WM LEFT OUTER JOIN
dbo.USER_REGISTRATION AS UR ON WM.TO_CONTACT_ID = UR.ID AND WM.RECIPIENT_CATEGORY_ID = 1 LEFT OUTER JOIN
dbo.TEACHER_REGISTRATION AS URR ON WM.FROM_CONTACT_ID = URR.ID  AND WM.RECIPIENT_CATEGORY_ID <> 1

Open in new window

0
mfreudenCommented:
You'll need to left outer join both tables like this

left outer join User_Registration UR
      on ur.id=wm.to_Contact_id
      and receipient_category_id=1
left outer join User_Registration URR
      on ur.id=wm.from_Contact_id
      and receipient_category_id<>1

Then use case statements or isnull () in the select clause to show the fields.  When UR.Field is null, then URR.Field will never be null and vice-versa.

select isnull (ur.Fullname,urr.FullName) as FullName.

or

select case when receipient_category_id=1 then ur.FullName else urr.FullName end
from ...
0
TECH_NETAuthor Commented:
sorry i messedup my question. For more clarity, the colum from_Contact_id will always fetch data from UR ( ie Student_registration) and the criteria i mentioned above is only for to_contact_id which could be link either to the student Regsitration(UR) or the teacher_registration(URR)


0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

JestersGrindCommented:
Are you saying that the query will always join to UR on from_Contact_id?  And then conditionally join to UR or URR depending on WM.RECIPIENT_CATEGORY_ID?  If that is the case, you need to join to USER_REGISTRATION twice with different aliases for each one.  

Greg


0
TECH_NETAuthor Commented:
Yes that is correct. Can you please modifiy the query and post it here.
0
JestersGrindCommented:
Try this.

Greg



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DOMAIN_WEBMAIL_VIEW]
AS
SELECT     
        WM.ID, 
        ISNULL(WM.FROM_CONTACT_ID,0) AS FROM_CONTACT_ID,
        ISNULL(UR.FULL_NAME,'') AS FROM_CONTACT,
        ISNULL(WM.RECIPIENT_CATEGORY_ID,0) AS RECIPIENT_CATEGORY_ID,
        ISNULL(WM.TO_CONTACT_ID,0) AS TO_CONTACT_ID,
        ISNULL(URR.FULL_NAME,'') AS TO_CONTACT,
        ISNULL(WM.MAIL_SUBJECT, '') AS MAIL_SUBJECT,
        ISNULL(WM.MAIL_BODY, '') AS MAIL_BODY,
        
 FROM         
dbo.DOMAIN_WEBMAIL AS WM INNER JOIN 
dbo.USER_REGISTRATION AS UR ON WM.FROM_CONTACT_ID = UR.ID LEFT OUTER JOIN
dbo.USER_REGISTRATION AS UR2 ON WM.TO_CONTACT_ID = UR2.ID AND WM.RECIPIENT_CATEGORY_ID = 1 LEFT OUTER JOIN
dbo.TEACHER_REGISTRATION AS URR ON WM.FROM_CONTACT_ID = URR.ID  AND WM.RECIPIENT_CATEGORY_ID <> 1

Open in new window

0
TECH_NETAuthor Commented:
this solution would help if i define 2 columns in the view

ie

ISNULL(WM.TO_CONTACT_ID,0) AS TO_CONTACT_ID,
ISNULL(URR.FULL_NAME,'') AS TO_CONTACT,
ISNULL(WM.TO_CONTACT_ID,0) AS TO_CONTACT_ID,
ISNULL(UR2.FULL_NAME,'') AS TO_CONTACT1,

Cannot i get the condition clause to get this  value in a single column of the view.
0
JestersGrindCommented:
Try this.

Greg



SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[DOMAIN_WEBMAIL_VIEW]
AS
SELECT     
        WM.ID, 
        ISNULL(WM.FROM_CONTACT_ID,0) AS FROM_CONTACT_ID,
        ISNULL(UR.FULL_NAME,'') AS FROM_CONTACT,
        ISNULL(WM.RECIPIENT_CATEGORY_ID,0) AS RECIPIENT_CATEGORY_ID,
        ISNULL(WM.TO_CONTACT_ID,0) AS TO_CONTACT_ID,
        CASE WHEN WM.RECIPIENT_CATEGORY_ID = 1 THEN ISNULL(UR2.FULL_NAME,'') ELSE ISNULL(URR.FULL_NAME,'') END  AS TO_CONTACT,
        ISNULL(WM.MAIL_SUBJECT, '') AS MAIL_SUBJECT,
        ISNULL(WM.MAIL_BODY, '') AS MAIL_BODY,
        
 FROM         
dbo.DOMAIN_WEBMAIL AS WM INNER JOIN 
dbo.USER_REGISTRATION AS UR ON WM.FROM_CONTACT_ID = UR.ID LEFT OUTER JOIN
dbo.USER_REGISTRATION AS UR2 ON WM.TO_CONTACT_ID = UR2.ID AND WM.RECIPIENT_CATEGORY_ID = 1 LEFT OUTER JOIN
dbo.TEACHER_REGISTRATION AS URR ON WM.FROM_CONTACT_ID = URR.ID  AND WM.RECIPIENT_CATEGORY_ID <> 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.