Self-join with more than 1 table? (Star schema)

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

General SQL Server Forums

New to SQL Server Programming

Self-join with more than 1 table? (Star schema)

Author

Topic

kgl_m
Starting Member

2 Posts

Posted - 2009-02-02 : 12:34:54

Hi all, I am trying to get a self-join working, the problem being that I don't have much experience in SQL Server.

I have a star schema set up - there are 4 dimension tables connected to a fact table and I'm querying one of those tables for something that would be a self-join in a simple world. However, since there is more than one table involved, I'm not sure how I should go about it.

This is what I have so far:

SELECT 
F1.Days_Out, F1.Sale_Date, 
E1.Engagement_ID, E1.FactSheet_Compare_To_Engagement_ID, 
Total=F1.Mail_Sales+f1.Group_Sales+f1.Phone_Sales+f1.Outlet_Sales+f1.BoxOffice_Sales+f1.Internet_Sales
      
  FROM [Datawarehouse].[dbo].[dimEngagement] as E1 
join [Datawarehouse].[dbo].[factAdvanceSale] as F1 on E1.dimEngagement_Key = F1.dimEngagement_Key
left outer join [Datawarehouse].[dbo].[dimEngagement] as E2 join [Datawarehouse].[dbo].[factAdvanceSale] as F2
on E1.Engagement_ID = E2.FactSheet_Compare_To_Engagement_ID

where E1.[Engagement_ID]=97493 order by F1.Days_Out desc

As you can see, it is a mish-mash and its no surprise that it doesn't work. It tells me "Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'where'."

What I want to do is join E1 to F1, and then self-join that beast back to itself and then query it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 12:38:19

[code]
SELECT
F1.Days_Out,
F1.Sale_Date,
E1.Engagement_ID,
E1.FactSheet_Compare_To_Engagement_ID,
Total=F1.Mail_Sales+f1.Group_Sales+f1.Phone_Sales+f1.Outlet_Sales+f1.BoxOffice_Sales+f1.Internet_Sales
FROM [Datawarehouse].[dbo].[dimEngagement] as E1
join [Datawarehouse].[dbo].[factAdvanceSale] as F1
on E1.dimEngagement_Key = F1.dimEngagement_Key
left outer join [Datawarehouse].[dbo].[dimEngagement] as E2
on condition missing here
join [Datawarehouse].[dbo].[factAdvanceSale] as F2
on E1.Engagement_ID = E2.FactSheet_Compare_To_Engagement_ID
where E1.[Engagement_ID]=97493
order by F1.Days_Out desc
[/code]
you've a on condition missing in between

kgl_m
Starting Member

2 Posts

Posted - 2009-02-02 : 13:44:55

Thanks visakh. Could you help me visualize what is happening in that nested join condition so I can decide what ON condition to put in there? I tried a couple of things and it didn't work.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-02 : 14:08:27

Try to go step by step. Execute the statement with juts a single join first, and then go from there. In many cases you will realize the 'JOIN' might not be needed at all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources