select all records from join with temp table

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

Transact-SQL (2005)

select all records from join with temp table

Author

Topic

GrandVizier
Starting Member

14 Posts

Posted - 2007-05-03 : 18:44:15

Hej
Back for some more advise on creating a valid select statement.
Here is sample data and the query I'm working with:

DECLARE @Agent TABLE (aID INT, aName VARCHAR(7))
INSERT @Agent
SELECT 35, 'HAN' UNION ALL
SELECT 36, 'OLD' UNION ALL
SELECT 37, 'BOAT' 

DECLARE	@Reservations TABLE (rID INT, rTourID INT, rAgentID INT, rPass INT,  rChild INT, rCoup INT, rTotal MONEY)
INSERT	@Reservations
SELECT	10, 64, 35, 5, 0, 0, 50		UNION ALL
SELECT	11, 60, 35, 1, 0, 0, 10		UNION ALL
SELECT	12, 64, 36, 2, 1, 0, 25.7	UNION ALL
SELECT	13, 62, 37, 0, 3, 2, 8.56	UNION ALL
SELECT	14, 64, 37, 2, 0, 1, 34		UNION ALL
SELECT	15, 66, 35, 6, 0, 0, 43.75	

DECLARE	@Tour TABLE (tID INT, tName VARCHAR(10),  tPickupID INT)
INSERT	@Tour
SELECT 60, 'Bike', 1 UNION ALL
SELECT 62, 'Bus', 3 UNION ALL
SELECT 64, 'Heli', 2 UNION ALL
SELECT 66, 'Walk', 1

DECLARE	@Pickup TABLE (pID INT, pName VARCHAR(15))
INSERT	@Pickup
SELECT 1, '1st Pickup' UNION ALL
SELECT 2, '2nd Pickup' UNION ALL
SELECT 3, 'Adv Pickup' 

SELECT  Pickup.pName, Agent.aName, 
		SUM(Res.rPass) AS Adl, SUM(Res.rChild) AS Chl, SUM(Res.rCoup) AS A_CPN, 
		SUM(Res.rPass + Res.rChild) AS PAX, 
		SUM(Res.rTotal) AS Sales, Tour.tPickupID
FROM   @Agent AS Agent INNER JOIN
		@Reservations AS Res ON Agent.AID = Res.rAgentID INNER JOIN
		@Tour AS Tour ON Tour.tID = Res.rTourID join
		@Pickup AS Pickup ON Tour.tPickupID = Pickup.pID
GROUP BY Pickup.pName, Agent.aName, Tour.tPickupID
ORDER BY Agent.aName

The output of that is looking like this:


pName 		aName	Adl	Chl 	A_CPN	PAX	Sales	tPickupID
2nd Pickup	BOAT	2	0	1	2	34.00	2
Adv Pickup	BOAT	0	3	2	3	8.56	3
1st Pickup	HAN	7	0	0	7	53.75	1
2nd Pickup	HAN	5	0	0	5	50.00	2
2nd Pickup	OLD	2	1	0	3	25.70	2

The way I want it to look is more like:


pName 		aName	Adl	Chl 	A_CPN	PAX	Sales	tPickupID
1st Pickup	BOAT	0	0	0	0	0.00	1 
2nd Pickup	BOAT	2	0	1	2	34.00	2
Adv Pickup	BOAT	0	3	2	3	8.56	3
1st Pickup	HAN	7	0	0	7	53.75	1
2nd Pickup	HAN	5	0	0	5	50.00	2
Adv Pickup	HAN	0	0	0	0	0.00	3
1st Pickup	OLD	2	1	0	3	0.00	1 
2nd Pickup	OLD	2	1	0	3	25.70	2
Adv Pickup	OLD	0	0	0	0	0.00	3

I've tried a couple techniques for creating a temp table to fill in, but I just seem to be getting the same results.
One method that I haven't been able to get to work but seems logical is to create a temp table with the above result columns, insert the default values for each row, then update the temp table with an aggregate function.
But since I'm probably making this much more difficult then I should be, I'm here looking for advise.
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 18:50:48

best bet is probably to have default rows in your results table and
then update them according to your wishes.

because this kind of logic if i understand it correctly isn't worth the complexity of writing in a single statement.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-04 : 00:33:45

DECLARE @Agent TABLE (aID INT, aName VARCHAR(7))
INSERT @Agent
SELECT 35, 'HAN' UNION ALL
SELECT 36, 'OLD' UNION ALL
SELECT 37, 'BOAT'

DECLARE @Reservations TABLE (rID INT, rTourID INT, rAgentID INT, rPass INT, rChild INT, rCoup INT, rTotal MONEY)
INSERT @Reservations
SELECT 10, 64, 35, 5, 0, 0, 50 UNION ALL
SELECT 11, 60, 35, 1, 0, 0, 10 UNION ALL
SELECT 12, 64, 36, 2, 1, 0, 25.7 UNION ALL
SELECT 13, 62, 37, 0, 3, 2, 8.56 UNION ALL
SELECT 14, 64, 37, 2, 0, 1, 34 UNION ALL
SELECT 15, 66, 35, 6, 0, 0, 43.75

DECLARE @Tour TABLE (tID INT, tName VARCHAR(10), tPickupID INT)
INSERT @Tour
SELECT 60, 'Bike', 1 UNION ALL
SELECT 62, 'Bus', 3 UNION ALL
SELECT 64, 'Heli', 2 UNION ALL
SELECT 66, 'Walk', 1

DECLARE @Pickup TABLE (pID INT, pName VARCHAR(15))
INSERT @Pickup
SELECT 1, '1st Pickup' UNION ALL
SELECT 2, '2nd Pickup' UNION ALL
SELECT 3, 'Adv Pickup'

Select
pa.pName ,
pa.aName,
Coalesce(Sum(r.rPass), 0) as Adl,
Coalesce(Sum(r.rChild), 0) as Chl,
Coalesce(Sum(r.rCoup), 0) as A_Cpn,
Coalesce(Sum(r.rTotal), 0.0000) as Sales,
t.tPickupID
from
(Select * from @pickup p cross join @Agent pic) pa
left outer join @tour t
on pa.pid = t.tPickupID
left outer join @Reservations r
on r.rTourID = t.tid and r.rAgentID = pa.aid and r.rTourID = t.tid
group by t.tPickupID, pa.pName,pa.aName
order by pa.aname

GrandVizier
Starting Member

14 Posts

Posted - 2007-05-04 : 12:23:53

ah see, there it is - thats exactly what I was looking for - thanks so much

of course now that I've come to implement it, I've found what I actually needed was a cross-tab report - so I've made the adjustments to get what I need but since this is new syntax for me I'm posting it here in case it could be simplified even more...

Select
pa.aName,
Coalesce((SUM(CASE t.tPickupID WHEN 1 THEN r.rPass ELSE 0 END)), 0) AS Ad1,
Coalesce((SUM(CASE t.tPickupID WHEN 1 THEN r.rChild ELSE 0 END)), 0) AS Ch1,
Coalesce((SUM(CASE t.tPickupID WHEN 2 THEN r.rPass ELSE 0 END)), 0) AS Ad2,
Coalesce((SUM(CASE t.tPickupID WHEN 2 THEN r.rChild ELSE 0 END)), 0) AS Ch2,
Coalesce((SUM(CASE t.tPickupID WHEN 3 THEN r.rPass ELSE 0 END)), 0) AS Ad3,
Coalesce((SUM(CASE t.tPickupID WHEN 3 THEN r.rChild ELSE 0 END)), 0) AS Ch3,
Coalesce(Sum(r.rCoup), 0) as A_Cpn,
Coalesce(Sum(r.rPass + r.rChild), 0) as Pax,
Coalesce(Sum(r.rTotal), 0.0000) as Sales
from
(Select * from @pickup p cross join @Agent pic) pa
left outer join @tour t
on pa.pid = t.tPickupID
left outer join @Reservations r
on r.rTourID = t.tid and r.rAgentID = pa.aid and r.rTourID = t.tid
group by pa.aName
order by pa.aname

Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources