Hi, i have a problem to select sum from same table using UNION. The key
question is there a way of combining two questions in one so the resulting
objectid in query1 is only used in query 2 in a smart way. Eg. if only one
object is avaliable in a search first year then only check that objectid for
next year and append the sum in the question.
I have 4 tables
companyobjects that contains the key (companyid,objectid,name etc)
and i have
calendar the table looks similar to this
(objectid,year,day1,day2.....day365)
price that looks similar to this (objectid,year,day1,day2.....day365)
reservation that looks similar to this (objectid,year,day1,day2.....day365)
I have tried as follows below, as you can see i want to calculate price
discount and amount to pay from same table but from 2 years. I have tried
removed all my "where" clause below and the result is not correct it is not
from both querys.
Maybe you know an easier way, totaly diffrent way that i have not thought of
perhaps.
Thanks in advance /Henrik
SELECT
`companyobjects`.`objectid`,
Sum(`objectprice`.`d362` +`objectprice`.`d363`
+`objectprice`.`d364`+`objectprice`.`d365`),
Sum((`objectprice`.`d362` +`objectprice`.`d363`
+`objectprice`.`d364`+`objectprice`.`d365`)*0.1),
Sum((`objectprice`.`d362` +`objectprice`.`d363`
+`objectprice`.`d364`+`objectprice`.`d365`)*0.9)
FROM
`companyobjects`
Inner Join `objectprice` ON `companyobjects`.`objectid` =
`objectprice`.`objectid`
Inner Join `objectreservation` ON `companyobjects`.`objectid` =
`objectreservation`.`objectid`
Inner Join `objectcalendar` ON `companyobjects`.`objectid` =
`objectcalendar`.`objectid`
WHERE
`companyobjects`.`companyid` = 'C050319112022656' AND
`companyobjects`.`maxnrofguests` >= '1' AND
`objectprice`.`year` = '2006' AND
`objectreservation`.`year` = '2006' AND
`objectcalendar`.`year` = '2006'
GROUP BY
`companyobjects`.`objectid`
UNION ALL
SELECT
`companyobjects`.`objectid`,
Sum(`objectprice`.`d1` +`objectprice`.`d2`),
Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.1),
Sum((`objectprice`.`d1` +`objectprice`.`d2`)*0.9)
FROM
`companyobjects`
Inner Join `objectprice` ON `companyobjects`.`objectid` =
`objectprice`.`objectid`
Inner Join `objectreservation` ON `companyobjects`.`objectid` =
`objectreservation`.`objectid`
Inner Join `objectcalendar` ON `companyobjects`.`objectid` =
`objectcalendar`.`objectid`
WHERE
`companyobjects`.`companyid` = 'C050319112022656' AND
`companyobjects`.`maxnrofguests` >= '1' AND
`objectprice`.`year` = '2007' AND
`objectreservation`.`year` = '2007' AND
`objectcalendar`.`year` = '2007'
GROUP BY
`companyobjects`.`objectid`
I
_________________________________________________________________
Hitta rätt på nätet med MSN Search http://search.msn.se/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
