SQL subquery problem

[Chris James]

I work with a Hotel MS SQL 6.5 Database System...
I have two tables... One is GUEST One is GROUPS

I can query the GUEST table for group(feb 99) reservation information for
every piece of info i need except for the actual name of the group---
which i need to get by quering the GROUPS table...

I query (and do a little math on) the GUEST table for rates, nights,
saacct(group number) and name which i am trying to get from the GROUPS table
by subquery, but it is not working....

Not sure if i am messing up any expressions or what....

SELECT 'Feb99' as Month, name as GroupName, sum(nights) as PU,
sum(rate*nights) as Revenue, sum(rate*nights) / sum(nights) as ADR
FROM guest
WHERE saacct is not null
and departure between '02/01/99' and '02/28/99'
and name in
(SELECT name
FROM groups
WHERE departure between '02/01/99' and '02/28/99'
and groups.saacct = guest.saacct)
GROUP by name
WITH rollup

If I leave out the subquery it does all the math CORRECTLY and picks out THE
CORRECT 12 groups for feb and rolls up all the sums and everything is peachy
but if i put in the subquery(so i can get the dang name too) i get only one group selected... and that group is not even a correct one...

Chris James.... help... thanks...

[Kenneth Wilhelmsson]

How about joining for the name instead?

SELECT 'Feb99' as Month, name as GroupName, sum(nights) as PU,
sum(rate*nights) as Revenue, sum(rate*nights) / sum(nights) as ADR
FROM guest INNER JOIN groups
ON groups.saacct = guest.saacct
WHERE guest.saacct is not null
and guest.departure between '02/01/99' and '02/28/99'
GROUP by name
WITH rollup

Not tested, but it should work.

/Kenneth

------------
Chris James at 4/8/99 11:25:10 AM


I work with a Hotel MS SQL 6.5 Database System...
I have two tables... One is GUEST One is GROUPS

I can query the GUEST table for group(feb 99) reservation information for
every piece of info i need except for the actual name of the group---
which i need to get by quering the GROUPS table...

I query (and do a little math on) the GUEST table for rates, nights,
saacct(group number) and name which i am trying to get from the GROUPS table
by subquery, but it is not working....

Not sure if i am messing up any expressions or what....

SELECT 'Feb99' as Month, name as GroupName, sum(nights) as PU,
sum(rate*nights) as Revenue, sum(rate*nights) / sum(nights) as ADR
FROM guest
WHERE saacct is not null
and departure between '02/01/99' and '02/28/99'
and name in
(SELECT name
FROM groups
WHERE departure between '02/01/99' and '02/28/99'
and groups.saacct = guest.saacct)
GROUP by name
WITH rollup

If I leave out the subquery it does all the math CORRECTLY and picks out THE
CORRECT 12 groups for feb and rolls up all the sums and everything is peachy
but if i put in the subquery(so i can get the dang name too) i get only one group selected... and that group is not even a correct one...

Chris James.... help... thanks...

Thanks Kennenth,

I will give it a try this weekend...
i was thinking about a join but i
could not think it through clearly
thanks... i will post the results

------------
Kenneth Wilhelmsson at 4/8/99 5:15:57 PM

How about joining for the name instead?

SELECT 'Feb99' as Month, name as GroupName, sum(nights) as PU,
sum(rate*nights) as Revenue, sum(rate*nights) / sum(nights) as ADR
FROM guest INNER JOIN groups
ON groups.saacct = guest.saacct
WHERE guest.saacct is not null
and guest.departure between '02/01/99' and '02/28/99'
GROUP by name
WITH rollup

Not tested, but it should work.

/Kenneth

------------
Chris James at 4/8/99 11:25:10 AM


I work with a Hotel MS SQL 6.5 Database System...
I have two tables... One is GUEST One is GROUPS

I can query the GUEST table for group(feb 99) reservation information for
every piece of info i need except for the actual name of the group---
which i need to get by quering the GROUPS table...

I query (and do a little math on) the GUEST table for rates, nights,
saacct(group number) and name which i am trying to get from the GROUPS table
by subquery, but it is not working....

Not sure if i am messing up any expressions or what....

SELECT 'Feb99' as Month, name as GroupName, sum(nights) as PU,
sum(rate*nights) as Revenue, sum(rate*nights) / sum(nights) as ADR
FROM guest
WHERE saacct is not null
and departure between '02/01/99' and '02/28/99'
and name in
(SELECT name
FROM groups
WHERE departure between '02/01/99' and '02/28/99'
and groups.saacct = guest.saacct)
GROUP by name
WITH rollup

If I leave out the subquery it does all the math CORRECTLY and picks out THE
CORRECT 12 groups for feb and rolls up all the sums and everything is peachy
but if i put in the subquery(so i can get the dang name too) i get only one group selected... and that group is not even a correct one...

Chris James.... help... thanks...