Cross Join + Union = pff, difficult

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 2000 Forums

Transact-SQL (2000)

Cross Join + Union = pff, difficult

Author

Topic

Razzmatazz
Starting Member

7 Posts

Posted - 2005-10-31 : 04:21:14

This is a part of my database:

I need to get the following:
A recordset containing 5 columns: globaladdition_id, arrangement_id, required, always, hotel_id

It contains every record the GlobalAddition_Arrangement table containts

The always column is set to 1 if the globaladdition_id also appears in the GlobalAddition_PricingMethod table, but only if the pricingmethod='PERARRANGEMENT'
Otherwise the always column can be 0 or null.

If a globaladdition_id is in the GlobalAddition_PricingMethod table (pricingmethod='PERARRANGEMENT'), a record should also be added for all arrangement_id's with corresponding hotel_id's. For these records the always column should be 1 and the required column should be null.

I probably want to put this in a view, and filter it on hotel_id. Linked GlobalAdditions and Arrangements always have the same hotel_id.

I've tried a couple of things, but I'm at a loss. Some help would be greatly appreciated.

Janne

I love deadlines: I love the whooshing sound they make as they go by.

-D.A.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-10-31 : 05:15:11

select g.globaladdition_id, g.arrangement_id, g.required,
always = case when exists (select * from
GlobalAddition_PricingMethod x
where x.globaladdition_id = g.globaladditino_id
and x.pricingmethod = 'PERARRANGEMENT') then
1
else
0
end,
a.hotel_id
from GlobalAddition_Arrangement g,
Arrangement a,
where g.arrangement_id = a.arrangement_id

[KH]

Razzmatazz
Starting Member

7 Posts

Posted - 2005-10-31 : 09:45:32

Thanks for the input, but it doesn't de everything. I've generated the SQL, so you can easily test.

CREATE TABLE [dbo].[Arrangement] (
	[arrangement_id] [int] IDENTITY (1, 1) NOT NULL ,
	[hotel_id] [int] NOT NULL 
)
GO
CREATE TABLE [dbo].[GlobalAddition] (
	[globaladdition_id] [int] IDENTITY (1, 1) NOT NULL ,
	[hotel_id] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[GlobalAddition_Arrangement] (
	[globaladdition_id] [int] NOT NULL ,
	[arrangement_id] [int] NOT NULL ,
	[required] [bit] NOT NULL 
)
GO
CREATE TABLE [dbo].[GlobalAddition_Pricingmethod] (
	[globaladdition_id] [int] NOT NULL ,
	[pricingmethod] [varchar] (14) 
)
GO
INSERT INTO [dbo].[Arrangement] VALUES (1)
INSERT INTO [dbo].[Arrangement] VALUES (1)
INSERT INTO [dbo].[Arrangement] VALUES (1)
INSERT INTO [dbo].[GlobalAddition] VALUES (1)
INSERT INTO [dbo].[GlobalAddition] VALUES (1)
INSERT INTO [dbo].[GlobalAddition] VALUES (1)
INSERT INTO [dbo].[GlobalAddition_PricingMethod] VALUES (2,'PERARRANGEMENT')
INSERT INTO [dbo].[GlobalAddition_PricingMethod] VALUES (3,'PERARRANGEMENT')
INSERT INTO [dbo].[GlobalAddition_Arrangement] VALUES (1,1,0)
INSERT INTO [dbo].[GlobalAddition_Arrangement] VALUES (1,2,1)
INSERT INTO [dbo].[GlobalAddition_Arrangement] VALUES (2,1,0)
INSERT INTO [dbo].[GlobalAddition_Arrangement] VALUES (2,2,1)

The execution of the query I seek after should return:


globaladdition_id, arrangement_id, required, always, hotel_id
1 , 1 , 0 , 0 , 1
1 , 2 , 1 , 0 , 1 
2 , 1 , 0 , 1 , 1
2 , 2 , 1 , 1 , 1
2 , 3 , null , 1 , 1
3 , 1 , null , 1 , 1
3 , 2 , null , 1 , 1
3 , 3 , null , 1 , 1

Your query only returns the first 4 records.

I love deadlines: I love the whooshing sound they make as they go by.

-D.A.

Razzmatazz
Starting Member

7 Posts

Posted - 2005-11-21 : 10:02:26

So, after three weeks I'm back on the project. C'mon people, let's solve this little challenge.

Razzmatazz
Starting Member

7 Posts

Posted - 2005-11-21 : 10:16:58

wow, I got in myself. I guess I'm much clearer then last time I tried it...


CREATE VIEW AllArrangementGlobaladdition
   ( globaladdition_id,
   arrangement_id,
   required,
   always,
   hotel_id )
AS
SELECT ga.globaladdition_id, ga.arrangement_id, ga.required,
   always = CASE WHEN EXISTS (SELECT * FROM GlobalAddition_PricingMethod gp
         WHERE gp.globaladdition_id = ga.globaladdition_id
         AND gp.pricingmethod = 'PERARRANGEMENT')
      THEN 1
      ELSE 0
   END,
   a.hotel_id
   FROM GlobalAddition_Arrangement ga
   LEFT JOIN Arrangement a ON ga.arrangement_id = a.arrangement_id
UNION
SELECT g.globaladdition_id, a.arrangement_id, required = null, always = 1, g.hotel_id
   FROM GlobalAddition g CROSS JOIN Arrangement a
   WHERE g.hotel_id = a.hotel_id
   AND EXISTS (SELECT * FROM GlobalAddition_PricingMethod gp
         WHERE gp.globaladdition_id = g.globaladdition_id
         AND gp.pricingmethod = 'PERARRANGEMENT')
   AND NOT EXISTS ( SELECT * FROM GlobalAddition_Arrangement ga
         WHERE ga.arrangement_id = a.arrangement_id
         AND ga.globaladdition_id = g.globaladdition_id )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources