| Re: Hjælp til SELECT med GROUP BY fra 2 tabeller |
|
 |
|
 |
|
 |
|
 |
Group: dk.edb.database · Group Profile
Author: FlemmingFlemming Date: Jan 23, 2007 11:27
>> Jeg prøver det på mit web-hoteel snarest. På Access (som jeg
>> bruger til små-øvelser, da det er lige ved hånden), var det
>> ikke supporteret.
>
> Hvilken base bruger du på webhotellet?
Jeg bruger mySQL 5.0 (på www.one.com). Den har tilsyneladende en anden
syntax, end MSSQL, men du ved - jo mindre haj, jo mindre abstraktionsevne,
og jo mindre skridt i afprøvningen.
> SELECT ISNULL(b.theyear, p.theyear) as theYear,
> b.antal as bAntal,
> p.antal as pAntal
> FROM
> (
> select year(oprettet) as theYear, count(*) as antal
> from banner
> where year(oprettet) < 2006
> group by year(oprettet)
> ) b
> FULL OUTER JOIN
> (
> select year(oprettet) as theYear, count(*) as antal
> from partnerlink
> group by year(oprettet)
> ) p
> ON b.theYear = p.theYear
>
Som - behørigt (?) tilrettet - ikke kører - ubestemmelig syntax-fejl. Synes
at kunne se på dokumentationen, at FULL OUTER JOIN ikke er supporteret.
Derfor er jeg gået til dit forslag:
SELECT * FROM
(
SELECT DISTINCT YEAR(PaintDate) as theYear
FROM pictures
UNION
SELECT DISTINCT YEAR(SalesDate) as theYear
FROM sales
) x
LEFT JOIN
( ... ) p ON x.theYear = p.theYear
LEFT JOIN
( ... ) s ON x.theYear = s.theYear
Er nået til, at
SELECT DISTINCT YEAR(PaintDate) as theYear
FROM pictures
UNION
SELECT DISTINCT YEAR(SalesDate) as theYear
FROM sales
fungerer, og giver årstallene, men
SELECT * FROM
(
SELECT DISTINCT YEAR(PaintDate) as theYear
FROM pictures
UNION
SELECT DISTINCT YEAR(SalesDate) as theYear
FROM sales
) x
giver syntax-fejl. Arbejder videre...
Tak for hjælpen - hver gang når jeg længere og forstår mere.
Flemming
| 
