[MySQL] Query met meerdere tabellen

[Verwijderd]

Database model:

code:

select  DISTINCT arpd.arnr39, arpd.arlv39, arpd.lvnr39, arpd.akpr39, 
        arpd.vkpr39, arpd.trpr39, arpd.ltnr39, arot.omsha3,
        arbs.gkpr30, arbs.vkvp30, arbs.vkpr30, arvr.vrkd35, arvr.clcm35,
        arom.jaar33, arlv.akvp31, arlv.akpr31, arbs.merk30, sums.arom33
        from arpd,
                (SELECT SUM(arom.AROM33) as arom33
                    FROM       arom, arpd
                    WHERE    arom.arnr33 = arpd.arnr39 
                    AND          arom.jaar33 = '2008'
                    AND NOT  arom.arom33 = '0'
                    AND      arpd.ltnr39 = 'TEST1') as sums
        inner join arbs on (arpd.arnr39 = arbs.arnr30 and arpd.lvnr39 = arbs.lvnr30)
        inner join arvr on (arpd.arnr39 = arvr.arnr35 and fmkd35 = 'IMM' and mgnr35 = 'HTT')
        inner join arom on (arpd.arnr39 = arom.arnr33 and jaar33 = '2008')
        inner join arlv on (arpd.arnr39 = arlv.arnr31 and arpd.lvnr39 = arlv.lvnr31)
        inner join arot on (arpd.arnr39 = arot.arnra3 and tlkda3 = 'N')
        where arpd.ltnr39 = 'TEST1'

deze query zou alles uit LotDetail moeten ophalen en dan uit de overige tabellen enkele gegevens bijvoegen. De query werkt zonder de "(SELECT SUM...)" na "from arpd,", de foutmelding die ik dan krijg is: "Unknown column 'aprd.arnr39' in 'on clause'"

Na veel opzoekwerk kom ik er maar niet uit. waar ligt hier de fout? en is dit ook een efficiënte wijze om deze query uit te voeren?

[whoami]

WTF zijn dat voor columnnames, zo wordt het wel leesbaar en makkelijk om je query eens te bekijken.

En wat is 'arpd' voor tabel ? Ik zie die niet in jouw schema ?
Waarom schrijf je 'AND NOT arom.arom33 = '0'' ipv AND arom.arom33 <> '0' ?
Waarom is (vrijwel) alles VARCHAR(45), ook al zijn het numerieke gegevens ?

[curry684]

Naast de WTF over de column names: zie ik daar primary keys bestaande uit 5 varchar(45)'s en 2 ints?

[dik_voormekaar]

Verwijderd schreef op donderdag 15 mei 2008 @ 23:17:
...
deze query zou alles uit LotDetail moeten ophalen en dan uit de overige tabellen enkele gegevens bijvoegen. De query werkt zonder de "(SELECT SUM...)" na "from arpd,", de foutmelding die ik dan krijg is: "Unknown column 'aprd.arnr39' in 'on clause'"

Na veel opzoekwerk kom ik er maar niet uit. waar ligt hier de fout? en is dit ook een efficiënte wijze om deze query uit te voeren?

Je hebt last van deze mysql bug:
http://bugs.mysql.com/bug.php?id=13551

[Voutloos]

Dat is dus geen mysql bug, iedereen die daar last van heeft, had al een lelijke query style onder mysql 4. Moet je maar joins netjes uitschrijven cq haakjes gebruiken.

Sowieso is het te makkelijk om het op een bug van de db te schuiven, het datamodel is dermate slordig dat queries helemaal niet relevant zijn.

[Verwijderd]

ik had de tabelnamen er nog niet bijgezet , de pk's zijn nu ook voorzien van de correcte datatypes. Dit is namelijk een as400 database waar ik niets aan kan veranderen. Ik zal dit testen op de as400, ik deed dit eerst op een lokale mysql, dus als dat een probleem is van mysql is dat niet zo erg.

de query zelf is deze goed opgebouwd of is er een andere (betere) manier om deze gegevens op te halen? (ARPD kan tot 35000 records hebben)

Volgende query werkt goed:

code:

select  distinct arpd.arnr39, arpd.arlv39, arpd.ltnr39, arpd.lvnr39,
        arpd.ktgp39, arpd.akpr39, arpd.vkpr39, arpd.trpr39, arlv.arnr31, arlv.lvnr31,
        arlv.arlv31, arlv.akvp31, arlv.akpr31, arbs.arnr30, arbs.gkpr30, arbs.vkvp30,
        arbs.vkpr30, arbs.merk30, arot.arnra3, arot.omsha3, arot.tlkda3, arvr.arnr35,
        arvr.fmkd35, arvr.mgnr35, arvr.clcm35, arvr.vrkd35, arom.arnr33
        from arpd as arpd
        inner join arbs as arbs on (arpd.arnr39 = arbs.arnr30 and arpd.lvnr39 = arbs.lvnr30)
        inner join arlv as arlv on (arpd.arnr39 = arlv.arnr31 and arpd.lvnr39 = arlv.lvnr31)
        inner join arot as arot on (arpd.arnr39 = arot.arnra3 and tlkda3 = 'N')
        inner join arvr as arvr on (arpd.arnr39 = arvr.arnr35 and fmkd35 = 'IMM' and mgnr35 = 'TTS')
        left join arom as arom on (arpd.arnr39 = arom.arnr33 and jaar33 = '2006')
        where arpd.ltnr39 = 'TEST1'

ik zou hier nu ook uit de AROM tabel per record hetvolgende willen als resultaat:
SELECT SUM(arom33)
FROM arom
WHERE JAAR33 = '2006'
AND ARNR33 = '001'
=> (ARNR33 = ARNR39)

hoe kan ik dit best doen?

[ Voor 55% gewijzigd door Verwijderd op 16-05-2008 14:44 ]

[-FoX-]

Is het ook een optie om deze gegevens uit meerdere query's te halen?
Misschien wordt het geheel dan nog wel trager, maar het maakt het langs de andere kant ook wel een stukje makkelijker?

[Verwijderd]

Het opsplitsen van de queries lijkt mij niet zo'n goed idee, want de query moet zo performant mogelijk blijven.

Mijn query werkt uiteindelijk:

code:

select distinct arpd.arnr39, arpd.arlv39, arpd.ltnr39, arpd.lvnr39, arpd.ktgp39, 
arpd.akpr39, arpd.vkpr39, arpd.trpr39, arlv.arnr31, arlv.lvnr31, arlv.arlv31, 
arlv.akvp31, arlv.akpr31, arbs.arnr30, arbs.gkpr30, arbs.vkvp30, arbs.vkpr30, 
arbs.merk30, arot.arnra3, arot.omsha3, arot.tlkda3, arvr.arnr35, arvr.fmkd35, 
arvr.mgnr35, arvr.clcm35, arvr.vrkd35, arom.arnr33, arom.arom33, aromp.arom33, 
arom.orat33, aromp.orat33 
from asqimalfts.arpd as arpd 
left join asqimalfts.arbs as arbs on (arpd.arnr39 = arbs.arnr30 and arpd.lvnr39 = arbs.lvnr30) 
left join asqimalfts.arlv as arlv on (arpd.arnr39 = arlv.arnr31 and arpd.lvnr39 = arlv.lvnr31) 
left join asqimalfts.arot as arot on (arpd.arnr39 = arot.arnra3 and tlkda3 = 'N') 
left join asqimalfts.arvr as arvr on (arpd.arnr39 = arvr.arnr35 and fmkd35 = 'IMM' and mgnr35 = 'TTS') 
left join ( select arom.arnr33 as arnr33, sum(arom.arom33) as arom33, SUM(arom.orat33) as orat33 from asqimalfts.arom as arom where jaar33 = '2006' group by arom.arnr33 ) arom on arom.arnr33 = arpd.arnr39 
left join ( select arom.arnr33 as arnr33, sum(arom.arom33) as arom33, SUM(arom.orat33) as orat33 from asqimalfts.arom as arom where jaar33 = '2005' group by arom.arnr33 ) aromp on aromp.arnr33 = arpd.arnr39
where arpd.ltnr39 = 'TEST1';

Bovenstaande query geeft mij 1773 records in 12 seconden, het maximum dat ooit zal opgehaald worden is 35.000 records en dan zit je al op een 237 seconden. Hierbij zijn de gegevens uit de ARPB tabel er nog niet bij, daarom dat deze query heel performant moet zijn.

Nu zou ik enkel nog de gegevens uit de tabel ARPB (Staffels) halen, maar dit kunnen er meerdere zijn per record uit bovenstaande query. Hoe kan ik dit best doen: per record een query voor alle records uit de ARPB tabel of met een join werken en dan dit in de code opvangen? Er bestaat misschien nog een betere werkwijze?

[Niemand_Anders]

Optimalisatie: Verplaats de "where arpd.ltnr39 = 'TEST1' " naar de eerste join clause. Op die manier beperk je direct als de initiele tabel waaraan de rest gekoppeld wordt. arpd.ltnr39 wordt namelijk in elk join query gebruikt...

Ik heb geen idee hoe groot je arpd tabel is, maar je haalt nu eerst alle records ervan op en join vervolgens 5 a 6 tabellen aan dat resultaat en pas daarna ga je filteren op dat ene record welke je nodig hebt.

[D-Raven]

as400.. i feel your pain, heb er helaas ook mee moeten werken. Deze dingen zijn vaak nog uit het stenen tijdperk met een datamodel to match...

Optimalisatie. Wat Niemand_Anders hier boven mij al zei.

Waar je ook naar kunt kijken is hoe de data word gepresenteerd.
Moet je per see alles in een keer weergeven? of is het mogelijk dat je je gegevens in batches van 1000 ophaalt. Op die manier heb je sneller een resultaat set terug om weer te geven en kun je dus sneller een response geven naar je gebruiker.

Maargoed of dit werkbaar is is natuurlijk afhankelijk van waarvoor je je data gebruikt.

[Verwijderd]

Voor het ophalen heb ik de query wat gewijzigd, het zijn nu allemaal left join's geworden, hiervoor moet de where dus ook op de ARPD blijven staan.

Voor de gegevens heb ik ook alle records nodig uit de query want dit moet weggeschreven worden in een excel file want ondertussen ook al gelukt is. Enkel de gegevens van de staffels tabel moeten er nog bij, maar hoe kan ik dit best doen?

[-FoX-]

Kan je die where clause dan niet gewoon in de left join query opnemen?

[Verwijderd]

Als ik de where clause in de left join plaats dan kan het zijn dat als deze tabel geen waarde heeft die dan ook niets ophaalt, dus die moet op de ARPD tabel blijven staan. (als het outer joins waren, zo was het eerst, dan was dit idd wel een betere oplossing)

Ik zit nu enkel nog met mijn ARPS tabel (staffels) waar er voor elke record in mijn ARPD er meerdere records in mijn ARPS kunnen staan. Ik denk dat als ik dit moet loopen in mijn code het niet meer performant zal zijn want ik moet dan per element een query doen naar dat database.

is dit niet rechtstreeks mogelijk met de query?
ik zou hiervoor wel een outer join kunnen bijplaatsen en dan in mijn code dit verder opvangen?

[ Voor 5% gewijzigd door Verwijderd op 29-05-2008 12:57 ]