11. november 2002 - 16:33Der er
13 kommentarer og 1 løsning
SUM i WHERE
Hej Eksperter Jeg har følgende Select:
SELECT MAX_MEETINGROOM_BOOKED.dateto,MAX_MEETINGROOM_BOOKED.datefrom, MAX_MEETINGROOM.* FROM MAX_MEETINGROOM_BOOKED INNER JOIN MAX_MEETINGROOM ON MAX_MEETINGROOM.id = MAX_MEETINGROOM_BOOKED.meetingroomid WHERE (SUM(MAX_MEETINGROOM.chairs - MAX_MEETINGROOM_BOOKED.bookedchairs)<=2) or datefrom >= '20021112 00:00' and dateto <= '20021112 23:59' AND (NOT ('20021112 11:30' BETWEEN datefrom AND dateto )) AND (NOT ('20021112 01:00' BETWEEN datefrom AND dateto )) order by meetingname, datefrom
Jeg har en SUM i min where clause. Det er den der giver mig problemer. Kan min select laves om så jeg godt må ha den med uden at jeg får fejlen:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Prøv at sætte SUM op i SELECT delen. F.eks. sådan her :
SELECT MAX_MEETINGROOM_BOOKED.dateto,MAX_MEETINGROOM_BOOKED.datefrom, MAX_MEETINGROOM.* FROM MAX_MEETINGROOM_BOOKED INNER JOIN MAX_MEETINGROOM ON MAX_MEETINGROOM.id = MAX_MEETINGROOM_BOOKED.meetingroomid, SUM(MAX_MEETINGROOM.chairs - MAX_MEETINGROOM_BOOKED.bookedchairs) as talt_sammen WHERE (talt_sammen<=2) or datefrom >= '20021112 00:00' and dateto <= '20021112 23:59' AND (NOT ('20021112 11:30' BETWEEN datefrom AND dateto )) AND (NOT ('20021112 01:00' BETWEEN datefrom AND dateto )) order by meetingname, datefrom
SELECT MAX_MEETINGROOM_BOOKED.dateto, MAX_MEETINGROOM_BOOKED.datefrom, MAX_MEETINGROOM.*, SUM(MAX_MEETINGROOM.chairs - MAX_MEETINGROOM_BOOKED.bookedchairs) AS talt_sammen FROM MAX_MEETINGROOM_BOOKED INNER JOIN MAX_MEETINGROOM ON MAX_MEETINGROOM.id = MAX_MEETINGROOM_BOOKED.meetingroomid WHERE (talt_sammen <= 2) OR datefrom >= '20021112 00:00' AND dateto <= '20021112 23:59' AND (NOT ('20021112 11:30' BETWEEN datefrom AND dateto)) AND (NOT ('20021112 01:00' BETWEEN datefrom AND dateto)) ORDER BY meetingname, datefrom
Det giver følgende fejl: Invalid column name 'talt_sammen'.
Hmmm.... Har ikke prøvet sådan noget før. Men hvad så hvis du sætter SUM i Select delen og samtidig i Where.
SELECT MAX_MEETINGROOM_BOOKED.dateto, MAX_MEETINGROOM_BOOKED.datefrom, MAX_MEETINGROOM.*, SUM(MAX_MEETINGROOM.chairs - MAX_MEETINGROOM_BOOKED.bookedchairs) AS talt_sammen FROM MAX_MEETINGROOM_BOOKED INNER JOIN MAX_MEETINGROOM ON MAX_MEETINGROOM.id = MAX_MEETINGROOM_BOOKED.meetingroomid WHERE (SUM(MAX_MEETINGROOM.chairs - MAX_MEETINGROOM_BOOKED.bookedchairs) <= 2) OR datefrom >= '20021112 00:00' AND dateto <= '20021112 23:59' AND (NOT ('20021112 11:30' BETWEEN datefrom AND dateto)) AND (NOT ('20021112 01:00' BETWEEN datefrom AND dateto)) ORDER BY meetingname, datefrom
FROM MAX_MEETINGROOM_BOOKED INNER JOIN MAX_MEETINGROOM ON MAX_MEETINGROOM.id = MAX_MEETINGROOM_BOOKED.meetingroomid
WHERE (MAX_MEETINGROOM.chairs - MAX_MEETINGROOM_BOOKED.bookedchairs<=2) or datefrom >= '20021112 00:00' and dateto <= '20021112 23:59' AND (NOT ('20021112 11:30' BETWEEN datefrom AND dateto )) AND (NOT ('20021112 01:00' BETWEEN datefrom AND dateto )) order by meetingname, datefrom
Som min select er nu henter jeg alle de poster der er i begge tabeller. Det skal være sådan at mit resultat også indeholder de mødelokaler der er i MAX_MEETINGROOM men som ikke er i MAX_MEETINGROOM_BOOKED. På dansk er det de mødelokaler der ikke er booked endnu den valgte dag.
FROM MAX_MEETINGROOM_BOOKED INNER JOIN MAX_MEETINGROOM ON MAX_MEETINGROOM.id = MAX_MEETINGROOM_BOOKED.meetingroomid
WHERE (MAX_MEETINGROOM.chairs - MAX_MEETINGROOM_BOOKED.bookedchairs<=2) or datefrom >= '20021112 00:00' and dateto <= '20021112 23:59' AND (NOT ('20021112 11:30' BETWEEN datefrom AND dateto )) AND (NOT ('20021112 01:00' BETWEEN datefrom AND dateto )) AND MAX_MEETINGROOM.id = 3 order by meetingname, datefrom
Jeg vil nu helst at jeg kun har et select at arbejde med. Det andet er vist noget fusk ;-) Jeg tror jeg laver et nyt spørgsmål, der skal nemlig også puttes noget mere ind i min select. Så kan du jo svare der hvis du vil. Men tak for hjælpen indtil videre.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
