duplicate rows selected but single row is desired

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)

duplicate rows selected but single row is desired

Author

Topic

GrandVizier
Starting Member

14 Posts

Posted - 2007-03-14 : 17:01:30

There are 3 tables (each of these have additional columns that aren't relevant to this query and have been left out of this example):
RESERVATIONS
rID | rDate | rName | rTotalPass

RES2
r2ID | rID | r2Pass | sID

SPECIAL
sID | sAdult | sChild | sInfant

The sAdult, sChild & sInfant are boolean values, which when linked to r2Pass allow you to determine the number of adults (or children) on the trip. For example, Reservation 1 is booked for 3/13/07 by Smith for 5 people (2 adults, 2 children, and 1 infant). This would like like the following in the DB:


RESERVATIONS
rID |  rDate  | rName | rTotalPass 
------------------------------------
1   | 3/13/07 | Smith |    5 

RES2 
r2ID | rID | r2Pass | sID 
---------------------------
 5  |  1  |    1    |  84 
 6  |  1  |    1    |  85
 7  |  1  |    2    |  71
 8  |  1  |    1    |  96

SPECIAL 
sID | sAdult | sChild | sInfant 
---------------------------------
 71 |   0   |   1    |   0  
 84 |   1   |   0    |   0  
 85 |   1   |   0    |   0  
 96 |   0   |   0    |   1

I'm looking for a query which I can run that will list the details of each reservation in a single row. Below is an example of a query that I've tried but doesn't work. I'm thinking I might need to use some form of a sub-query, but I'm not sure how to implement one.


SELECT Res.rID, Res.rDate, Res.rName, Res.rTotalPass AS Total
  , case when Special.sAdult = 1 
    then Res2.r2Pass 
    else '0' end as Ad 
  , case when Special.sChild = 1 
    then Res2.r2Pass 
    else '0' end as Child 
  , case when Special.sInfant = 1 
    then Res2.r2Pass 
    else '0' end as Inf 
From Reservations Res INNER JOIN (Res2 LEFT JOIN Special ON (Res2.sID= Special.sID)) ON Res.rID = Res2.rID

I get results like:


rID |  rDate  |   rName   | Total | Ad | Child | Inf 
------------------------------------------------
387 | 01/01/06 | Harrison |   3   |  3 |  0  |  0 
418 | 01/01/06 |  Jones   |   5   |  0 |  2  |  0 
418 | 01/01/06 |  Jones   |   5   |  3 |  0  |  0

where I would like to see results like:


rID |  rDate  |   rName   | Total | Ad | Child | Inf 
------------------------------------------------
387 | 01/01/06 | Harrison |   3   |  3 |  0  |  0 
418 | 01/01/06 |  Jones   |   5   |  3 |  2  |  0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 17:12:10

[code]-- Prepare sample data
DECLARE @Reservations TABLE (rID INT, rDate DATETIME, rName VARCHAR(5), rTotalPass INT)

INSERT @Reservations
SELECT 1, '3/13/07', 'Smith', 5

DECLARE @Res2 TABLE (r2ID INT, rID INT, r2Pass INT, sID INT)

INSERT @Res2
SELECT 5, 1, 1, 84 UNION ALL
SELECT 6, 1, 1, 85 UNION ALL
SELECT 7, 1, 2, 71 UNION ALL
SELECT 8, 1, 1, 96

DECLARE @Special TABLE (sID INT, sAdult TINYINT, sChild TINYINT, sInfant TINYINT)

INSERT @Special
SELECT 71, 0, 1, 0 UNION ALL
SELECT 84, 1, 0, 0 UNION ALL
SELECT 85, 1, 0, 0 UNION ALL
SELECT 96, 0, 0, 1

-- Show the expected output
SELECT Res.rID,
Res.rDate,
Res.rName,
Res.rTotalPass AS Total,
SUM(CASE WHEN Special.sAdult = 1 THEN Res2.r2Pass ELSE 0 END) AS Ad,
SUM(CASE WHEN Special.sChild = 1 THEN Res2.r2Pass ELSE 0 END) AS Child,
SUM(CASE WHEN Special.sInfant = 1 THEN Res2.r2Pass ELSE 0 END) AS Inf
FROM @Reservations AS Res
INNER JOIN @Res2 AS Res2 ON Res2.rID = Res.rID
LEFT JOIN @Special AS Special ON Special.sID = Res2.sID
GROUP BY Res.rID,
Res.rDate,
Res.rName,
Res.rTotalPass
ORDER BY Res.rID,
Res.rDate,
Res.rName,
Res.rTotalPass DESC[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources