asked on

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

JestersGrind

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

mfreuden

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 ...

TECH_NET

ASKER

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)

JestersGrind

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

TECH_NET

ASKER

Yes that is correct. Can you please modifiy the query and post it here.

JestersGrind

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

TECH_NET

ASKER

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.

ASKER CERTIFIED SOLUTION

JestersGrind

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial