---

Hejsa

MySQL 4.1.15 (og PHP, men det bliver først relevant hvis det her ikke
kan lade sig gøre)

Jeg har følgende tabeller (som er relevante for spørgsmålet) :

_campaigns_
ID,Title, StartDay, EndDay.... osv

_campaignParticipants_
ID, AutoID, CampaignID, osv osv

_autos_
AutoID, CustomerID


Jeg vil gerne kunne lave en liste med alle campaigns som er til rådighed
(tilmeldte eller ej) for et givet CustomerIDs biler.

De tilmeldte biler er lette nok - der kan jeg joine på
campaignParticipants: (Det følgende er et minimalt select, i den
egentlige query udtrækker jeg selvfølgelig en masse mere)

SELECT campaigns.ID AS CID, autos.AutoID AS AutoID
FROM campaigns
LEFT JOIN campaignParticipants ON
campaignParticipants.CampaignID=campaigns.ID
LEFT JOIN autos ON campaignParticipants.AutoID=autos.AutoID AND
autos.CustomerID=14

- men kan jeg komme afsted med at hente 1 række ud pr AutoID i autos
(altså: 1 campagne og 2 biler hvoraf fx. 1 er tilmeldt, for en given
kunde (CustomerID) giver 2 rækker)?

mvh

Jesper Brunholm

---

Jesper Brunholm skrev:

> _campaigns_
> ID,Title, StartDay, EndDay.... osv
>
> _campaignParticipants_
> ID, AutoID, CampaignID, osv osv
>
> _autos_
> AutoID, CustomerID



> Jeg vil gerne kunne lave en liste med alle campaigns som er
> til rådighed (tilmeldte eller ej) for et givet CustomerIDs
> biler.

Hvordan ser man om en kampagne er "til rådighed" hvis den ikke er
tilmeldt?

Leder du efter kampagner hvor der deltager mindst én af den biltype
som kunden har?

Prøv evt. at give nogle eksempler på data og ønsket resultat.
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

Jens Gyldenkærne Clausen skrev:
> Jesper Brunholm skrev:
>
>> _campaigns_
>> ID,Title, StartDay, EndDay.... osv
>>
>> _campaignParticipants_
>> ID, AutoID, CampaignID, osv osv
>>
>> _autos_
>> AutoID, CustomerID
>
>> Jeg vil gerne kunne lave en liste med alle campaigns som er
>> til rådighed (tilmeldte eller ej) for et givet CustomerIDs
>> biler.
>
> Hvordan ser man om en kampagne er "til rådighed" hvis den ikke er
> tilmeldt?

Ved at den findes (i den her sammenhæng, i praksis er der et "released"
felt som skal være før dags dato, men det er ikke relevant her, da det
ikke gør nogen forskel)

> Leder du efter kampagner hvor der deltager mindst én af den biltype
> som kunden har?

Nej, jeg leder efter alle campagner for alle biler sådan så jeg kan lave
listen:

"Din liste af tilgængelige kampagner:"

Kampagne 1 - NN 23 712 [en stak data om kampagnen og bilen, blandt andet
om den er tilmeldt, eller link til at tilmelde bilen til kampagnen]

Kampagne 1 - XN 12 345 [ -do- ]

Kampagne 2 - NN 23 712 [... osv

Kampagne 2 - XN 12 345 [... osv

> Prøv evt. at give nogle eksempler på data og ønsket resultat.

Ovenstående resultat skal gerne komme uanset om denne kundes to biler er
tilmeldte (dvs har en række i campaignParticipants) eller ej.

Dvs flg datasæt skal gerne give resultatet, faktisk uafhængigt af hvad
der befinder sig i campaignParticipants.

_campaigns_
ID: 1
ID: 2

_campaignParticipants_
ID: 1 | AutoID: XN 12 345 | CampaignID: 1

_autos_
ID: XN 12 345 | CustomerID: 14
ID: NN 23 712 | CustomerID: 14


Jeg håber det er klart

mvh

Jesper Brunholm

PS. ovenstående nummerplader er komplet fiktive, systemet skal bruges i
holland, i forbindelse med http://www.ruitreclame.nl/

---

Jesper Brunholm skrev:

> Nej, jeg leder efter alle campagner for alle biler sådan så
> jeg kan lave listen:
>
> "Din liste af tilgængelige kampagner:"

Ah - så tror jeg at jeg er med.

Du vil (hvis jeg forstår det korrekt) have en liste med samtlige
aktive kampagner parret med samtlige biler. Af listen skal man så
kunne se om bilen medvirker i kampagnen eller ej.

Grundlisten er bare et CROSS JOIN af biler og kampagner, men det
giver ikke den ønskede oplysning om hvorvidt bilen deltager i
kampagnen. I stedet kan du kombinere den forespørgsel der finder
tilmeldte kampagner med en forespørgsel der finder resten:

SELECT campaignsID, cp.AutoID, 1 as tilmeldt
FROM campaignParticipants cp

UNION

SELECT campaigns.ID As CID, autos.AutoID, 0 as tilmeldt
FROM campaigns c
CROSS JOIN autos a
WHERE NOT EXISTS (
   SELECT 1 FROM campaignParticipant
   WHERE campaignID = c.ID
   AND autoID = a.autoID
)

Subquery'en i 2. del af union-forespørgslen er muligvis en hæmsko i
forhold til performance. En alternativ metode er at medtage
samtlige kombinationer i 2. del og så bruge aggregatfunktioner til
at komme tilbage til én række pr. bil/kampagne-par:


SELECT campaignsID, AutoID, SUM(tilmeldt) as tilmeldt
FROM
(
   SELECT campaignsID, cp.AutoID, 1 as tilmeldt
   FROM campaignParticipants cp

   UNION

   SELECT campaigns.ID As CID, autos.AutoID, 0 as tilmeldt
   FROM campaigns c
   CROSS JOIN autos a
) x
GROUP BY campaignsID, AutoID



> Jeg håber det er klart

Det vil vise sig...
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

Jens Gyldenkærne Clausen skrev:
> Du vil (hvis jeg forstår det korrekt) have en liste med samtlige
> aktive kampagner parret med samtlige biler. Af listen skal man så
> kunne se om bilen medvirker i kampagnen eller ej.

Lige præcis :)

Jeg skrev spørgsmålet her for at lære noget SQL som jeg ikke havde styr
på, jeg kan se at jeg har fået kam til mit hår, men foreløbig hænger jeg
vist på :)

> kampagnen. I stedet kan du kombinere den forespørgsel der finder
> tilmeldte kampagner med en forespørgsel der finder resten:
>
> SELECT campaignsID, cp.AutoID, 1 as tilmeldt
> FROM campaignParticipants cp
>
> UNION
>
> SELECT campaigns.ID As CID, autos.AutoID, 0 as tilmeldt
> FROM campaigns c
> CROSS JOIN autos a
> WHERE NOT EXISTS (
>    SELECT 1 FROM campaignParticipant
>    WHERE campaignID = c.ID
>    AND autoID = a.autoID
> )

Den havde to problemer:
1) MySQL mente (vist fuldt forståeligt) ikke at den kendte til kolonnen
campaignsID. Rettelse til campaigns.ID hjalp ikke før jeg tilføjede et
JOIN imellem campaignParticipants og campaigns oppe i første SELECT
halvdel (hvor "c" ikke er tilgængelig, hvis jeg har forstået UNION
funktionaliteten nogenlunde?). Senere gik det op for mig at du snarere
havde tænkt på CampaignID, som er det jeg har brugt i nedenstående løsning
2) den manglende hookup på et CustomerID.

Jeg har løst begge dele:

SELECT CampaignID, cp.AutoID, 1 as tilmeldt
FROM campaignParticipants cp
INNER JOIN autos ON cp.AutoID=autos.AutoID AND autos.CustomerID=14

UNION

SELECT c.ID As CID, a.AutoID, 0 as tilmeldt
FROM campaigns c
CROSS JOIN autos a
WHERE NOT EXISTS (
   SELECT 1 FROM campaignParticipants
   WHERE campaignID = c.ID
   AND autoID = a.autoID
)
AND a.CustomerID=14

- så ser det til gengæld ud til at det kører som det skal, jeg får det
rigtige antal rækker ud i resultatet, og performance behøver jeg vist
ikke bekymre mig om før vi har mindst 10+ kampagner og 100+ kunder der
kan tænkes at spørge på deres muligheder samtidig, da den her trækker 10
kampagner for en kunde med 2 biler ud på under 0.002 sekunder.

> En alternativ metode er at medtage
> samtlige kombinationer i 2. del og så bruge aggregatfunktioner til
> at komme tilbage til én række pr. bil/kampagne-par:
>
> SELECT campaignsID, AutoID, SUM(tilmeldt) as tilmeldt
> FROM
> (
>    SELECT campaignsID, cp.AutoID, 1 as tilmeldt
>    FROM campaignParticipants cp
>
>    UNION
>
>    SELECT campaigns.ID As CID, autos.AutoID, 0 as tilmeldt
>    FROM campaigns c
>    CROSS JOIN autos a
> ) x
> GROUP BY campaignsID, AutoID

Hvad gør "x" på næstsidste linje?

Jeg tror jeg har forstået resten af hvad der foregår, og det ser ud til
at jeg får hvad jeg skal have med:

SELECT CampaignID, AutoID, SUM(tilmeldt) as tilmeldt
FROM
(
   SELECT CampaignID, cp.AutoID, 1 as tilmeldt
   FROM campaignParticipants cp
   INNER JOIN autos ON cp.AutoID=autos.AutoID AND autos.CustomerID=14

   UNION

   SELECT c.ID As CID, a.AutoID, 0 as tilmeldt
   FROM campaigns c
   INNER JOIN autos a ON a.CustomerID=14
) x
GROUP BY CampaignID, AutoID

- så skal jeg "bare" have "forlænget" en af dem til at jeg får de øvrige
data jeg behøver fra campaigns, autos og campaignParticipants, jeg vil
ikke garantere at jeg ikke vender tilbage med problemer . Da nummer
to her tager dobbelt så lang tid med samme CustomerID, bliver det nok
den øverste jeg arbejder videre med (jeg kan se at konklusionen er stik
modsat hvis jeg ikke indskrænker på CustomerID)

mvh

Jesper Brunholm

---

Jesper Brunholm skrev:
> SELECT CampaignID, cp.AutoID, 1 as tilmeldt
> FROM campaignParticipants cp
> INNER JOIN autos ON cp.AutoID=autos.AutoID AND autos.CustomerID=14
>
> UNION
>
> SELECT c.ID As CID, a.AutoID, 0 as tilmeldt
> FROM campaigns c
> CROSS JOIN autos a
> WHERE NOT EXISTS (
> SELECT 1 FROM campaignParticipants
> WHERE campaignID = c.ID
> AND autoID = a.autoID
> )
> AND a.CustomerID=14

Jeg har et problem med "forlængningen": jeg får den ene bil ud to gange,
den værdi som er forskellig i de to udtræk er KmBeg (som har de to
eksisterende værdier i campaignParticipants, men burde være tom eftersom
denne bil slet ikke er forekommende i campaignParticipants)

SQL:

SELECT campaigns.ID AS CampaignID, DATE_FORMAT(StartDay,'%d.%m.%y') AS
StartDay, DATE_FORMAT(EndDay, '%d.%m.%y') AS EndDay,
DATE_FORMAT(DeadlineIn, '%d.%m.%y') AS DeadlineIn, CONCAT(Title,'
',DATE_FORMAT(StartDay,'%d.%m.%y → '), DATE_FORMAT(EndDay,
'%d.%m.%y'),'. ',advertisers.Name) AS Campaign,
cp.AutoID, 1 as tilmeldt, KmBeg, KmEnd, PhotoBack, PhotoKmBeg,
PhotoKmEnd, AllDoneOK, DATE_FORMAT(StickerIsSend, '%d.%m.%y') AS
StickerIsSend, MoneyTransfered,
CONCAT(autos.Licenseplate, ' - ',Brand) AS Auto
FROM campaigns
INNER JOIN advertisers ON campaigns.AdvertiserID=advertisers.ID
INNER JOIN campaignParticipants cp ON campaigns.ID=cp.CampaignID
INNER JOIN autos ON cp.AutoID=autos.AutoID AND autos.CustomerID=14

UNION

SELECT c.ID As CID, DATE_FORMAT(StartDay,'%d.%m.%y') AS StartDay,
DATE_FORMAT(EndDay, '%d.%m.%y') AS EndDay, DATE_FORMAT(DeadlineIn,
'%d.%m.%y') AS DeadlineIn, CONCAT(Title,'
',DATE_FORMAT(StartDay,'%d.%m.%y → '), DATE_FORMAT(EndDay,
'%d.%m.%y'),'. ',advertisers.Name) AS Campaign,
a.AutoID, 0 as tilmeldt,
KmBeg, KmEnd, PhotoBack, PhotoKmBeg, PhotoKmEnd, AllDoneOK,
DATE_FORMAT(StickerIsSend, '%d.%m.%y') AS StickerIsSend, MoneyTransfered,
CONCAT(a.Licenseplate, ' - ',a.Brand) AS Auto
FROM campaigns c
INNER JOIN advertisers ON c.AdvertiserID=advertisers.ID
INNER JOIN campaignParticipants ON c.ID=campaignParticipants.CampaignID
CROSS JOIN autos a
WHERE NOT EXISTS (
   SELECT 1 FROM campaignParticipants
   WHERE campaignID = c.ID
   AND autoID = a.autoID
)
AND a.CustomerID=14 AND c.CampaignRelease <= 20061128 AND
advertisers.Release <= 20061128
ORDER BY StartDay

Jeg vil helst slippe for at fjerne "overflødige elementer", men hvis det
er nødvendigt for at kunne gennemskue det, må jeg jo prøve :)

Jeg har på fornemmelsen at problemet er at jeg ikke har fået joined
campaignParticipants rigtigt i anden halvdel (jeg var nødt til at tage
den med for at kunne få identisk antal felter til UNION), eller ikke
bruger NOT EXISTS rigtigt, men jeg kan ikke løse det.

mvh

Jesper Brunholm

---

Jesper Brunholm skrev:

> 1) MySQL mente (vist fuldt forståeligt) ikke at den kendte til
> kolonnen campaignsID.

[snip]

> Senere gik det op for mig at du snarere havde tænkt på
> CampaignID,

Præcis. Pointen er at det var unødvendigt med et join når man kun
skal have fat i id-værdien.


> 2) den manglende hookup på et CustomerID.


Jeg havde tænkt at man kunne udvælge customerID på "ydersiden" at
det view der finder kombinationerne. Altså sådan at viewet viser
alle kombinationer for alle kunder, og man så ved at selecte et
bestemt kundeID får reduceret det til en enkelt kunde.

Men det er nok lettere at håndtere når man fra starten indarbejder
en bestemt kunde - ellers kommer union-operatoren til at skulle
tjekke en ekstra parameter.


> SELECT c.ID As CID, a.AutoID, 0 as tilmeldt
> FROM campaigns c
> CROSS JOIN autos a
> WHERE NOT EXISTS (
> SELECT 1 FROM campaignParticipants
> WHERE campaignID = c.ID
> AND autoID = a.autoID
> )
> AND a.CustomerID=14

MySQL-fortolkeren vil sikkert selv sørge for at optimere, men
generelt bør du placere simple kriterier (custumerID = 14) før dyre
kriterier (not exists).



[snip]

>> SELECT campaigns.ID As CID, autos.AutoID, 0 as
>> tilmeldt FROM campaigns c
>> CROSS JOIN autos a
>> ) x
>> GROUP BY campaignsID, AutoID
>
> Hvad gør "x" på næstsidste linje?

Det er et alias for parentesen (kan også skrives "As x"). Jeg er
ikke sikker på at det er nødvendigt i MySQL, men jeg mener at der
er databaser der brokker sig hvis man ikke bruger alias på sådan en
subselect. Under alle omstændigheder skal aliaset bruges hvis man
skal joine med andre tabeller.



> - så skal jeg "bare" have "forlænget" en af dem til at jeg får
> de øvrige data jeg behøver fra campaigns, autos og
> campaignParticipants,

Brug den simple udgave (med få felter) som grundlag, og sæt resten
på med joins. Du kan evt. gemme det som et view (nå nej, det er
vist ikke med før v. 5 i mysql?)
--
Bolig søges. Andel eller leje i Emdrup, Nordvest, Nørrebro, Søborg
eller Brønshøj, max 6000 pr. måned.
Kontakt pr. mail - nospam(at)gyros.dk
Jens Gyldenkærne Clausen

---

Jens Gyldenkærne Clausen skrev:
>> 2) den manglende hookup på et CustomerID.
>
> Jeg havde tænkt at man kunne udvælge customerID på "ydersiden" at
> det view der finder kombinationerne. Altså sådan at viewet viser
> alle kombinationer for alle kunder, og man så ved at selecte et
> bestemt kundeID får reduceret det til en enkelt kunde.

Men så er det vel netop at man laver noget ressourcetungt ud af noget
der ikke behøver være det, eller er det fordi jeg ikke arbejder med
views, at jeg ikke ser pointen?

> MySQL-fortolkeren vil sikkert selv sørge for at optimere, men
> generelt bør du placere simple kriterier (custumerID = 14) før dyre
> kriterier (not exists).

Tak

>>> SELECT campaigns.ID As CID, autos.AutoID, 0 as
>>> tilmeldt FROM campaigns c
>>> CROSS JOIN autos a
>>> ) x
>>> GROUP BY campaignsID, AutoID
>> Hvad gør "x" på næstsidste linje?
>
> Det er et alias for parentesen (kan også skrives "As x").

Ahh - ok. Jeg har ikke prøvet uden, så jeg ved ikke om MySQL kan uden
alias for subselecten.

> subselect. Under alle omstændigheder skal aliaset bruges hvis man
> skal joine med andre tabeller.

- og det kunne så til gengæld gå hen og blive relevant når jeg - i
morgen - forsøger at få dit nye forslag til at virke.

Jeg har desværre først fået dine to indlæg her til aften (lidt mystisk,
for jeg kiggede ret omhyggeligt efter dem kl 15 til 16), og mit hoved
har simpelt hen taget time out.

> Brug den simple udgave (med få felter) som grundlag, og sæt resten
> på med joins. Du kan evt. gemme det som et view (nå nej, det er
> vist ikke med før v. 5 i mysql?)

Nej, det er ganske rigtigt først med fra 5'eren

Tak igen, for hjælpen så langt, jeg bliver en del klogere, det er ikke
så længe siden jeg begyndte at have MySQL 4 og dermed subselects til
rådighed, så jeg skal lige vænne mig til de kraftigt udvidede muligheder
for query-opbygning som det giver.

mvh

Jesper Brunholm

---

Jesper Brunholm skrev:

> Jeg har på fornemmelsen at problemet er at jeg ikke har fået
> joined campaignParticipants rigtigt i anden halvdel (jeg var
> nødt til at tage den med for at kunne få identisk antal felter
> til UNION)

Jeg vil foreslå dig en helt anden strategi (som nævnt i sidste
indlæg) - brug den simple udgave til at vælge de rigtige poster og
benyt joins til at få koblet ekstraoplysninger på.

I pseudo-kode:

SELECT <lang feltliste>
FROM
   (select der finder medvirkende biler
   union
   select der finder resten) x
LEFT JOIN oplysningstabel1 o
   ON x.someID = o.someID
LEFT JOIN oplysningstabel2 o2
   ON x.someID = o2.someID
....


Det bliver noget rod når du skal bruge union på så store
selectlister.
--
Bolig søges. Andel eller leje i Emdrup, Nordvest, Nørrebro, Søborg
eller Brønshøj, max 6000 pr. måned.
Kontakt pr. mail - nospam(at)gyros.dk
Jens Gyldenkærne Clausen

---

Jens Gyldenkærne Clausen skrev:

> Jeg vil foreslå dig en helt anden strategi (som nævnt i sidste
> indlæg) - brug den simple udgave til at vælge de rigtige poster og
> benyt joins til at få koblet ekstraoplysninger på.

> I pseudo-kode:
>
> SELECT <lang feltliste>
> FROM
>    (select der finder medvirkende biler
>    union
>    select der finder resten) x
> LEFT JOIN oplysningstabel1 o
>    ON x.someID = o.someID
> LEFT JOIN oplysningstabel2 o2
>    ON x.someID = o2.someID

> Det bliver noget rod når du skal bruge union på så store
> selectlister.

Ja, det kan jeg godt se.

Jeg har prøvet med lidt forskelligt, og har problemer med at få LEFT
JOIN campaignParticipants til at virke så jeg kun får indholdet ud i
rækken med den tilmeldte bil.

Følgende er min idé om hvordan man kunne få den rigtige række fra
campaignParticipants med, men det giver (rimeligt nok) "invalid use of
group function"

SELECT DATE_FORMAT(campaigns.StartDay,'%d.%m.%y') AS StartDay,
DATE_FORMAT(EndDay, '%d.%m.%y') AS EndDay, DATE_FORMAT(DeadlineIn,
'%d.%m.%y') AS DeadlineIn, CONCAT(Title,'
',DATE_FORMAT(StartDay,'%d.%m.%y &rarr; '), DATE_FORMAT(EndDay,
'%d.%m.%y'),'. ',advertisers.Name) AS Campaign,
cpa.CampaignID, SUM(tilmeldt) as tilmeldt,
autos.AutoID, CONCAT(Licenseplate, ' - ',Brand) AS Auto,
campaignParticipants.KmBeg, KmEnd, PhotoBack, PhotoKmBeg, PhotoKmEnd,
AllDoneOK, DATE_FORMAT(StickerIsSend, '%d.%m.%y') AS StickerIsSend,
MoneyTransfered
FROM
(
SELECT cp.CampaignID, cp.AutoID, 1 as tilmeldt
FROM campaignParticipants cp
INNER JOIN autos ON cp.AutoID=autos.AutoID AND autos.CustomerID=14

   UNION

SELECT c.ID As CID, a.AutoID, 0 as tilmeldt
FROM campaigns c
INNER JOIN autos a ON a.CustomerID=14
   LEFT JOIN campaignParticipants x ON c.ID=x.CampaignID
) AS cpa
INNER JOIN campaigns ON cpa.CampaignID=campaigns.ID
INNER JOIN advertisers ON campaigns.AdvertiserID = advertisers.ID
INNER JOIN autos ON cpa.AutoID = autos.AutoID
LEFT JOIN campaignParticipants ON
cpa.CampaignID=campaignParticipants.CampaignID AND SUM(tilmeldt) = 1

WHERE CampaignRelease < 20070201 AND Release <20070201
GROUP BY CampaignID, AutoID

Jeg har prøvet at joine med cpa (UNION SELECT'en), som giver forkert
indhold (og jeg tror godt jeg kan forstå hvorfor)

Jeg har prøvet at joine med campaign.ID = campaignParticipants.ID, og så
får jeg campaignParticipant-indhold ud til såvel tilmeldte biler (hvor
jeg skal have det) som ikke tilmeldte biler (som ikke bør have noget
campaignParticipants indhold).

Al hjælp værdsættes!

mvh

Jesper Brunholm




- og næsten alt ser ud som det skal, men jeg får

---

Jesper Brunholm skrev:
> Jeg har prøvet med lidt forskelligt, og har problemer med at få LEFT
> JOIN campaignParticipants til at virke så jeg kun får indholdet ud i
> rækken med den tilmeldte bil.

Jeg har nu fået det til at virke (jeg trængte vist bare til en pause og
noget søvn). Logikken bag løsningen er at jeg nødvendigvis skal have
noget som er udenfor UNION-SELECT'en (da denne har alt, og således også
irrelevant indhold), og at jeg skulle have fat i en parameter for at det
var en tilmeldt bils række.
Den havde Jens GC nydeligt (sikkert forudset behovet for) givet mig med
"tilmeldt", så den problematiske campaignParticipants join bliver:

LEFT JOIN campaignParticipants ON
cpa.CampaignID=campaignParticipants.CampaignID AND tilmeldt = 1

- og altså den samlede Query:

SELECT DATE_FORMAT(campaigns.StartDay,'%d.%m.%y') AS StartDay,
DATE_FORMAT(EndDay, '%d.%m.%y') AS EndDay, DATE_FORMAT(DeadlineIn,
'%d.%m.%y') AS DeadlineIn, CONCAT(Title,'
',DATE_FORMAT(StartDay,'%d.%m.%y &rarr; '), DATE_FORMAT(EndDay,
'%d.%m.%y'),'. ',advertisers.Name) AS Campaign,
cpa.CampaignID, SUM(tilmeldt) as tilmeldt,
autos.AutoID, CONCAT(Licenseplate, ' - ',Brand) AS Auto,
campaignParticipants.KmBeg, KmEnd, PhotoBack, PhotoKmBeg, PhotoKmEnd,
AllDoneOK, DATE_FORMAT(StickerIsSend, '%d.%m.%y') AS StickerIsSend,
MoneyTransfered
FROM
(
SELECT cp.CampaignID, cp.AutoID, 1 as tilmeldt
FROM campaignParticipants cp
INNER JOIN autos ON cp.AutoID=autos.AutoID AND autos.CustomerID=14

   UNION

SELECT c.ID As CID, a.AutoID, 0 as tilmeldt
FROM campaigns c
INNER JOIN autos a ON a.CustomerID=14
   LEFT JOIN campaignParticipants x ON c.ID=x.CampaignID
) AS cpa
INNER JOIN campaigns ON cpa.CampaignID=campaigns.ID
INNER JOIN advertisers ON campaigns.AdvertiserID = advertisers.ID
INNER JOIN autos ON cpa.AutoID = autos.AutoID
LEFT JOIN campaignParticipants ON
cpa.CampaignID=campaignParticipants.CampaignID AND tilmeldt = 1

WHERE CampaignRelease < 20070201 AND Release <20070201
GROUP BY CampaignID, AutoID


Tak for hjælpen, og hvis nogen kan se at jeg "misbruger" min MySQL ved
at sætte ovenstående sammen på en uhensigtsmæssig måde så hører jeg da
gerne om det

mvh

Jesper Brunholm

---

Jesper Brunholm skrev:

> - og altså den samlede Query:
>
> SELECT DATE_FORMAT(campaigns.StartDay,'%d.%m.%y') AS StartDay,
> DATE_FORMAT(EndDay, '%d.%m.%y') AS EndDay,

[snip]

Jeg kan ikke få alle dine felter i selectlisten til at hænge sammen
med:

> GROUP BY CampaignID, AutoID

- der tilsyneladende står i hovedforespørgslen (altså ikke "pakket
ind" i en subquery).

Har jeg overset noget, eller er der en klippe-klistre-fejl?
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

Jens Gyldenkærne Clausen skrev:
> Jesper Brunholm skrev:
>
>> - og altså den samlede Query:
>>
>> SELECT DATE_FORMAT(campaigns.StartDay,'%d.%m.%y') AS StartDay,
>> DATE_FORMAT(EndDay, '%d.%m.%y') AS EndDay,
>
> [snip]
>
> Jeg kan ikke få alle dine felter i selectlisten til at hænge sammen
> med:
>
>> GROUP BY CampaignID, AutoID
>
> - der tilsyneladende står i hovedforespørgslen (altså ikke "pakket
> ind" i en subquery).
>
> Har jeg overset noget, eller er der en klippe-klistre-fejl?

Jeg tror du har overset linje 5 og 6:

cpa.CampaignID, SUM(tilmeldt) as tilmeldt,
autos.AutoID, CONCAT(Licenseplate, ' - ',Brand) AS Auto,

- eller også misforstår jeg dit spørgsmål?

mvh

Jesper Brunholm

---

Jesper Brunholm skrev:

> Jeg tror du har overset linje 5 og 6:
>
> cpa.CampaignID, SUM(tilmeldt) as tilmeldt,
> autos.AutoID, CONCAT(Licenseplate, ' - ',Brand) AS Auto,


Nej, dem så jeg godt.


Det jeg undrer mig over er at du kan have StartDay, EndDay,
DeadlineIn, Title, KmBeg, KmEnd og mange flere felter med i en
forespørgsel der grupperer på CampaignID og AutoID uden at
aggregere førnævnte felter.

Det burde i mit hoved give en fejl.



Sammenlign med følgende helt simple eksempel:

   SELECT navn, postnr, COUNT(*) as antal
   FROM personer
   GROUP BY postnr

Hvilken post skal levere værdien til navn (forudsat at der er flere
poster med samme postnr)?

Grundreglen ved GROUP BY-forespørgsler er at et felt i selectlisten
enten skal være omfattet af group by-linjen eller også være
aggregeret (pakket ind i COUNT, SUM eller lignende).
--
Bolig søges. Andel eller leje i Emdrup, Nordvest, Nørrebro, Søborg
eller Brønshøj, max 6000 pr. måned.
Kontakt pr. mail - nospam(at)gyros.dk
Jens Gyldenkærne Clausen

---

Jens Gyldenkærne Clausen skrev:
> Det jeg undrer mig over er at du kan have StartDay, EndDay,
> DeadlineIn, Title, KmBeg, KmEnd og mange flere felter med i en
> forespørgsel der grupperer på CampaignID og AutoID uden at
> aggregere førnævnte felter.

Jeg vil undskylde hvis jeg svarer i øst, som du spørger i vest, jeg
forsøger det bedste jeg kan, og jeg er lidt handikappet af absolut
_ikke_ at være vant til at bruge nested SELECTs og aggregering i større
udstrækning.

Som jeg ser det, fungerer det at hente StartDay osv ud uden at skulle
have dem forbi en GROUP fordi de bliver hentet ud af en tabel som kun er
med i resultatet 1 gang.

LEFT JOIN campaignParticipants ON
cpa.CampaignID=campaignParticipants.CampaignID AND tilmeldt = 1

Det kan godt være at det i virkeligheden er en "skjult aggregering" at
jeg har den ON betingelse "tilmeldt = 1"?

Jeg har lagt et print-view af et resultat op her:
<http://www.ruitreclame.nl/campaign_auto_sql_result.php>

Hvordan ville du have lavet queryen sådan så problemet med StartDay osv
ikke opstod (og skal jeg lave et png databasediagram for at gøre det
lettere at svare på det spørgsmål, jeg er meget tæt på at lave et
alligevel...)?

> Sammenlign med følgende helt simple eksempel:
>
>    SELECT navn, postnr, COUNT(*) as antal
>    FROM personer
>    GROUP BY postnr
>
> Hvilken post skal levere værdien til navn (forudsat at der er flere
> poster med samme postnr)?

Det kan jeg godt se og forstå, men det kniber for mig med også at have
rutine og overblik til så at bruge den grundregel (som jeg lader stå her
nedenfor) til at overskue hvad jeg så bør og kan gøre - selv på den
lille eksempel-query her. Jeg kan godt se at jeg kan køre GROUP BY
postnr, navn, men jeg kan ikke få hovedet skruet på plads til at bruge
aggregeringsfunktioner som løser problemet.
Nok tildels fordi jeg ikke er rutineret i at bruge aggregeringsfunktionerne.

> Grundreglen ved GROUP BY-forespørgsler er at et felt i selectlisten
> enten skal være omfattet af group by-linjen eller også være
> aggregeret (pakket ind i COUNT, SUM eller lignende).

Der er ikke klippe-klistre-fejl, det er den query som jeg har sat ind,
som jeg er endt med at have liggende (foreløbig i et test-script).

Endnu en gang tak for al hjælp so far!

mvh

Jesper Brunholm

---

Jesper Brunholm skrev:

> Som jeg ser det, fungerer det at hente StartDay osv ud uden at
> skulle have dem forbi en GROUP fordi de bliver hentet ud af en
> tabel som kun er med i resultatet 1 gang.

Databasen har ingen mulighed for at se om et ikke-aggregeret felt
kun optræder med én værdi pr. gruppe.

Nu blev jeg nysgerrig, og tog et kig på MySQL's glimrende
manualsider. Svaret på mystikken ligger her:
http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html

MySQL tillader at bruge ikke-aggregerede felter i select-listen -
derfor fungerer din forespørgsel. De ikke-aggregerede felter bliver
ikke aggregeret skjult - men MySQL vælger bare en enkelt post ud
til værdien. Det vil fungere fint så længe værdien er konstant for
en gruppe (som den så vidt jeg kan overskue også er i dit
tilfælde).

Det er ganske praktisk, selv om det ikke er standard sql og selv om
man risikerer gale resultater hvis det bruges forkert.


> Hvordan ville du have lavet queryen sådan så problemet med
> StartDay osv ikke opstod

I traditionel sql ville man tilføje de (gruppevis) statiske felter
til GROUP BY-linjen.

Altså noget i retning af:

SELECT DATE_FORMAT( campaigns.StartDay , '%d.%m.%y' ) AS sd,
   DATE_FORMAT( EndDay , '%d.%m.%y' ) AS ed,
   ...
   SUM(tilmeldt) as Tilmeldt
FROM ...
GROUP BY sd, ed, ...

(Argh! - nu bliver jeg i tvivl om der skal grupperes efter det
beregnede feltudtryk (sd) eller man kan nøjes med de bagvedliggende
felter (StartDay) - jeg tror det er som jeg har skrevet).

Alle de felter der indgår i select-listen skal således enten være
aggregerede eller omfattet af GROUP BY-linjen. Kun på den måde kan
man sikre at databasen ved hvilken post der skal levere værdien til
et givent felt. MySQL's løsning er performancemæssig attraktiv, men
den introducerer så samtidig en fejlkilde hvis man glemmer en
nødvendig gruppering.
--
Jens Gyldenkærne Clausen
Svar venligst under det du citerer, og citer kun det der er
nødvendigt for at forstå dit svar i sammenhængen. Se hvorfor og
hvordan på http://usenet.dk/netikette/citatteknik.html

---

Jens Gyldenkærne Clausen skrev:
>> Som jeg ser det, fungerer det at hente StartDay osv ud uden at
>> skulle have dem forbi en GROUP fordi de bliver hentet ud af en
>> tabel som kun er med i resultatet 1 gang.
>
> Databasen har ingen mulighed for at se om et ikke-aggregeret felt
> kun optræder med én værdi pr. gruppe.

> Nu blev jeg nysgerrig, og tog et kig på MySQL's glimrende
> manualsider. Svaret på mystikken ligger her:
> http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html
>
> MySQL tillader at bruge ikke-aggregerede felter i select-listen -
> derfor fungerer din forespørgsel. De ikke-aggregerede felter bliver
> ikke aggregeret skjult - men MySQL vælger bare en enkelt post ud
> til værdien. Det vil fungere fint så længe værdien er konstant for
> en gruppe (som den så vidt jeg kan overskue også er i dit
> tilfælde).
>
> Det er ganske praktisk, selv om det ikke er standard sql og selv om
> man risikerer gale resultater hvis det bruges forkert.

Det forklarer en del, herunder at jeg også tidligere har lavet noget
lignende (altså et udtræk med "utilstrækkeligt aggregeret"). Det
forklarer også hvorfor jeg ikke kunne forstå dig fuldt ud, du lød som om
databasen burde give en fejlmelding - og jeg kunne se at den ikke gjorde
det

>> Hvordan ville du have lavet queryen sådan så problemet med
>> StartDay osv ikke opstod
>
> I traditionel sql ville man tilføje de (gruppevis) statiske felter
> til GROUP BY-linjen.
>
> Altså noget i retning af:
>
> SELECT DATE_FORMAT( campaigns.StartDay , '%d.%m.%y' ) AS sd,
>    DATE_FORMAT( EndDay , '%d.%m.%y' ) AS ed,
>    ...
>    SUM(tilmeldt) as Tilmeldt
> FROM ...
> GROUP BY sd, ed, ...

> (Argh! - nu bliver jeg i tvivl om der skal grupperes efter det
> beregnede feltudtryk (sd) eller man kan nøjes med de bagvedliggende
> felter (StartDay) - jeg tror det er som jeg har skrevet).

Jeg kan gøre begge dele i den her MySQL. Jeg er ret sikker på at jeg i
MySQL 3.26 ville være nødt til at bruge de bagvedliggende felter.

Findes standarden for standard-SQL online, så man kan slå op hvordan det
bør være i forhold til den?

> Alle de felter der indgår i select-listen skal således enten være
> aggregerede eller omfattet af GROUP BY-linjen. Kun på den måde kan
> man sikre at databasen ved hvilken post der skal levere værdien til
> et givent felt. MySQL's løsning er performancemæssig attraktiv, men
> den introducerer så samtidig en fejlkilde hvis man glemmer en
> nødvendig gruppering.

Taget i betragtning at der er sådan nogle halvamatører som mig (især som
jeg var hvad SQL angår for 1,5 år siden), som ikke læser MySQL manualen
igennem til bunds, blandt brugerne, er det nok lidt risky

Mange tak for hjælp og vejledning

Jesper Brunholm

---

Jesper Brunholm skrev:

> Findes standarden for standard-SQL online, så man kan slå op
> hvordan det bør være i forhold til den?

Kun delvist. Først og fremmest er det værd at notere sig at selv om
der findes officielle SQL-standarder - først og fremmest SQL-92 og
SQL:1999 og SQL:2003 (tallene angiver årstal) - er der mange
databaseprogrammer der (stadig) afviger fra dem.

Sammenlignet med browsernes understøttelse af html/css er der
væsentlig større forskelle mellem databaseprogrammer og de
bagvedliggende standarder.

Den SQL-standard der formentlig er bredest understøttet er SQL-92 -
der fastlægger hovedparten af den basale sql-syntaks. Den kan ses i
en rå tekst-udgave her:
<http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt>

De senere udgaver er ikke frit tilgængelige, men man kan dog hente
en "draft" af 2003-standarden[1].

Troels Arvin har lavet en glimrende sammenligning af 5 af de mest
udbredte databaseprogrammer, holdt op mod sql-standarden og mod
hinanden. Den kan varmt anbefales:
<http://troels.arvin.dk/db/rdbms/>

En side som jeg personligt godt kunne bruge, var en liste over
MySQL's "sidespring" i forhold til standarden. Udover det aktuelle
med GROUP BY-forespørgsler gælder det ting som håndteringen af
datoer (hvor man fx kan gemme 0-datoer og datoer som '2006-11-31'),
manglende fejlmeldinger ved data overflow o.l. Mens jeg skrev dette
faldt jeg faktisk over sådan en side:
<http://sql-info.de/mysql/gotchas.html>. Den nævner ikke GROUP BY,
men har en del andre særheder - også flere jeg ikke kendte til før.


> Taget i betragtning at der er sådan nogle halvamatører som mig
> (især som jeg var hvad SQL angår for 1,5 år siden), som ikke
> læser MySQL manualen igennem til bunds, blandt brugerne, er
> det nok lidt risky

MySQL er på godt og ondt en særdeles tilgivende database. Jeg har
hovedsageligt arbejdet i MSSQL, og det præger givetvis min tilgang
til databaser. Jeg kan sagtens se at MySQL's "gotchas" giver nogle
smarte muligheder i praksis, men jeg er nu glad for at det program
jeg arbejder med ikke er så tilgivende.


> Mange tak for hjælp og vejledning

Velbekomme.

Noter:
======
[1] se <http://en.wikipedia.org/wiki/SQL:2003>
--
Bolig søges. Andel eller leje i Emdrup, Nordvest, Nørrebro, Søborg
eller Brønshøj, max 6000 pr. måned.
Kontakt pr. mail - nospam(at)gyros.dk
Jens Gyldenkærne Clausen