Forum Mjukvara Programmering och digitalt skapande Tråd

MYSQL(php), Effektivaste sättet för info från 4 tabeller.

1
Skriv svar
Permalänk
Medlem
●

MYSQL(php), Effektivaste sättet för info från 4 tabeller.

Börjar med att posta en bild av tabellerna.

Antal = Antal av vardera artikel, utlämnat eller inlämnat.
Art_id = Artikel id/nr
for_id = Företags id

Jag vill subtrahera incheck2.antal mot utcheck2.antal, gruppera på art_id så jag får en summa för varje artikel, där for_id = '1'.

Jag har kommit så långt att jag summerar ihop utcheck korrekt.

$query = mysql_query("SELECT utcheck2.art_id, SUM(utcheck2.antal) FROM utcheck LEFT JOIN utcheck2 ON utcheck.id = utcheck2.utcheck_id WHERE utcheck.for_id = '1' GROUP BY utcheck2.art_id")or die(mysql_error());

Jag kommit fram till att jag sedan ska ta

(SUM(utcheck2.antal) - SUM(incheck2.antal)) AS nytt_antal

för att få fram mitt nya antal.
Däremot har jag ingen aning om hur jag ska joina ihop alla 4 tabellerna.

Alternativ nr 2.
Jag lägger in kolumnen for_id även i tabell incheck2 och utcheck2. Känns dock aningen ineffektivt att samla information dubbelt?

Alternativ nr 3.
Jag summera ihop först ihop antalet i utcheck2 som jag gjort enligt ovan query. Under min While-loop kör jag en subquery där jag för varje artikel plockar ut antalet ur incheck2 för att få min summa. Känns ineffektivt, även om det för tillfället finns få artiklar.

Alternativ nr 4.
Jag pillar ihop det med javascript då jag definierar varje antalet från tabell utcheck som antal$art_id, alltså antal1 = 200, antal2 = 400 osv.
Kör sen samma fast för tabell incheck och därefter summerar ihop. Känns inte heller jätteeffektivt?

Vill ni kära medmänniskor peka mig i rätt riktning?

Visa signatur

Livets 3 pelare:
SEX, MAT & KAFFE.

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
Skrivet av Lurcazzus:

Börjar med att posta en bild av tabellerna.
http://s8.postimage.org/z5o9xax9d/tabeller.jpg

Antal = Antal av vardera artikel, utlämnat eller inlämnat.
Art_id = Artikel id/nr
for_id = Företags id

Jag vill subtrahera incheck2.antal mot utcheck2.antal, gruppera på art_id så jag får en summa för varje artikel, där for_id = '1'.

Jag har kommit så långt att jag summerar ihop utcheck korrekt.

$query = mysql_query("SELECT utcheck2.art_id, SUM(utcheck2.antal) FROM utcheck LEFT JOIN utcheck2 ON utcheck.id = utcheck2.utcheck_id WHERE utcheck.for_id = '1' GROUP BY utcheck2.art_id")or die(mysql_error());

Jag kommit fram till att jag sedan ska ta

(SUM(utcheck2.antal) - SUM(incheck2.antal)) AS nytt_antal

för att få fram mitt nya antal.
Däremot har jag ingen aning om hur jag ska joina ihop alla 4 tabellerna.

Alternativ nr 2.
Jag lägger in kolumnen for_id även i tabell incheck2 och utcheck2. Känns dock aningen ineffektivt att samla information dubbelt?

Alternativ nr 3.
Jag summera ihop först ihop antalet i utcheck2 som jag gjort enligt ovan query. Under min While-loop kör jag en subquery där jag för varje artikel plockar ut antalet ur incheck2 för att få min summa. Känns ineffektivt, även om det för tillfället finns få artiklar.

Alternativ nr 4.
Jag pillar ihop det med javascript då jag definierar varje antalet från tabell utcheck som antal$art_id, alltså antal1 = 200, antal2 = 400 osv.
Kör sen samma fast för tabell incheck och därefter summerar ihop. Känns inte heller jätteeffektivt?

Vill ni kära medmänniskor peka mig i rätt riktning?

Gå till inlägget

Ja, jag ser att det blir riktigt klurigt med querys.

Jag passar på att kommentera din struktur.
Ganska svårtolkade tabeller med tanke på deras namn men av att granska datat så ser det ut som att det finns fyra tabeller som berör ett lagerliknande system. Incheckningstilfälle och incheckade produkter samt likadan struktur för utcheckning.
Det går att göra en förenkling. Baka ihop allt till två tabeller med lite mer passande namn.
Lagertillfälle och Lagertillfällerad (på samma sätt som man skulle göra Order och Orderrad)
Man kan alltså tänka på det som ett konto där man har saldo (lagersaldo) där det kan subtraheras och adderas över en tid.

Lagertillfalle
lagertillfalle_id, anv_id, datum, tid

Lagertillfallerad
lagertillfalle_id, art_id, antal

antal kan variera mellan negativt och positivt antal.

Då blir dina frågor genast mycket enklare. Det du försöker göra är troligtvis denna query:
select art_id, sum(antal) from lagertillfallerad where lagertillfalle_id = 1 group by art_id

Dessutom så finns det nu automatiskt stöd för att checka in och ut varor vid samma lagertillfälle.

Om du mot förmodan MÅSTE ha incheckning och utcheckning som helt separata tabeller (jag skulle sätta ett status på lagertillfälle-tabellen som bestämmer utcheck/incheck annars) så kan du i alla fall baka ihop incheck2 och utcheck2 till en tabell. Då blir queryn identisk som den jag skrev om du använder for_id istället för lagertillfalle_id.

Visa signatur

ηλί, ηλί, λαμά σαβαχθανί!?

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
Skrivet av Lurcazzus:

Börjar med att posta en bild av tabellerna.
http://s8.postimage.org/z5o9xax9d/tabeller.jpg

Antal = Antal av vardera artikel, utlämnat eller inlämnat.
Art_id = Artikel id/nr
for_id = Företags id

Jag vill subtrahera incheck2.antal mot utcheck2.antal, gruppera på art_id så jag får en summa för varje artikel, där for_id = '1'.

Jag har kommit så långt att jag summerar ihop utcheck korrekt.

$query = mysql_query("SELECT utcheck2.art_id, SUM(utcheck2.antal) FROM utcheck LEFT JOIN utcheck2 ON utcheck.id = utcheck2.utcheck_id WHERE utcheck.for_id = '1' GROUP BY utcheck2.art_id")or die(mysql_error());

Jag kommit fram till att jag sedan ska ta

(SUM(utcheck2.antal) - SUM(incheck2.antal)) AS nytt_antal

för att få fram mitt nya antal.
Däremot har jag ingen aning om hur jag ska joina ihop alla 4 tabellerna.

Alternativ nr 2.
Jag lägger in kolumnen for_id även i tabell incheck2 och utcheck2. Känns dock aningen ineffektivt att samla information dubbelt?

Alternativ nr 3.
Jag summera ihop först ihop antalet i utcheck2 som jag gjort enligt ovan query. Under min While-loop kör jag en subquery där jag för varje artikel plockar ut antalet ur incheck2 för att få min summa. Känns ineffektivt, även om det för tillfället finns få artiklar.

Alternativ nr 4.
Jag pillar ihop det med javascript då jag definierar varje antalet från tabell utcheck som antal$art_id, alltså antal1 = 200, antal2 = 400 osv.
Kör sen samma fast för tabell incheck och därefter summerar ihop. Känns inte heller jätteeffektivt?

Vill ni kära medmänniskor peka mig i rätt riktning?

Gå till inlägget

Detta borde fungera:

SELECT `ut`.art_id, (`ut`.sum_antal - `in`.sum_antal) kvar
FROM   (SELECT art_id, SUM(antal) sum_antal FROM utcheck2
        INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id
        WHERE for_id = 1
        GROUP BY art_id) `ut`,
       (SELECT art_id, SUM(antal) sum_antal FROM incheck2
        INNER JOIN incheck ON incheck.id = incheck2.incheck_id
        WHERE for_id = 1
        GROUP BY art_id) `in`
WHERE `ut`.art_id = `in`.art_id
Senast redigerat
Visa signatur

Kom-pa-TI-bilitet

Redigera
Citera flera Citera
Permalänk
Medlem
●
Skrivet av Leedow:

Ja, jag ser att det blir riktigt klurigt med querys.

Jag passar på att kommentera din struktur.
Ganska svårtolkade tabeller med tanke på deras namn men av att granska datat så ser det ut som att det finns fyra tabeller som berör ett lagerliknande system. Incheckningstilfälle och incheckade produkter samt likadan struktur för utcheckning.
Det går att göra en förenkling. Baka ihop allt till två tabeller med lite mer passande namn.
Lagertillfälle och Lagertillfällerad (på samma sätt som man skulle göra Order och Orderrad)
Man kan alltså tänka på det som ett konto där man har saldo (lagersaldo) där det kan subtraheras och adderas över en tid.

Lagertillfalle
lagertillfalle_id, anv_id, datum, tid

Lagertillfallerad
lagertillfalle_id, art_id, antal

antal kan variera mellan negativt och positivt antal.

Då blir dina frågor genast mycket enklare. Det du försöker göra är troligtvis denna query:
select art_id, sum(antal) from lagertillfallerad where lagertillfalle_id = 1 group by art_id

Dessutom så finns det nu automatiskt stöd för att checka in och ut varor vid samma lagertillfälle.

Om du mot förmodan MÅSTE ha incheckning och utcheckning som helt separata tabeller (jag skulle sätta ett status på lagertillfälle-tabellen som bestämmer utcheck/incheck annars) så kan du i alla fall baka ihop incheck2 och utcheck2 till en tabell. Då blir queryn identisk som den jag skrev om du använder for_id istället för lagertillfalle_id.

Gå till inlägget

Tänkte inte ens så långt som att min väll genomtänkta tabeller skulle vara dumt konstruerade :D.
anv_id = id:et på den som fört in det i systemet, så man vet vem som gjort fel om något är fel infört.
for_id = id:et på det företaget som fått något in eller utcheckat.

Jag skulle kunna lösa det med att alla utcheck eller incheck får ett minus resultat, men det blir konstigt när datan ska representeras i andra sammanhang och sen finns där ett förköpt antal som ska räknas med.

Förköpt antal artiklar per företag är en egen tabell, ett senare bekymmer ;).

Förköpt antal - (utcheckat - incheckat) ska helst bli ett positivt värde alltså, för att veta att det aktuella företaget inte har hämtat ut fler tallrikar än de köpt in.

Jag tackar så mycket för din hjälp och ska fundera kring om jag inte ska göra om mina tabeller :).

Visa signatur

Livets 3 pelare:
SEX, MAT & KAFFE.

Redigera
Citera flera Citera
Permalänk
Medlem
●
Skrivet av Teknocide:

Detta borde fungera:

SELECT `ut`.art_id, (`ut`.sum_antal - `in`.sum_antal) kvar
FROM   (SELECT art_id, SUM(antal) sum_antal FROM utcheck2
        INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id
        WHERE for_id = 1
        GROUP BY art_id) `ut`,
       (SELECT art_id, SUM(antal) sum_antal FROM incheck2
        INNER JOIN incheck ON incheck.id = incheck2.incheck_id
        WHERE for_id = 1
        GROUP BY art_id) `in`
WHERE `ut`.art_id = `in`.art_id
Gå till inlägget

Underbart, visste inte att man kunde göra på det sättet

Visa signatur

Livets 3 pelare:
SEX, MAT & KAFFE.

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
Skrivet av Lurcazzus:

Tänkte inte ens så långt som att min väll genomtänkta tabeller skulle vara dumt konstruerade :D.
anv_id = id:et på den som fört in det i systemet, så man vet vem som gjort fel om något är fel infört.
for_id = id:et på det företaget som fått något in eller utcheckat.

Jag skulle kunna lösa det med att alla utcheck eller incheck får ett minus resultat, men det blir konstigt när datan ska representeras i andra sammanhang och sen finns där ett förköpt antal som ska räknas med.

Förköpt antal artiklar per företag är en egen tabell, ett senare bekymmer ;).

Förköpt antal - (utcheckat - incheckat) ska helst bli ett positivt värde alltså, för att veta att det aktuella företaget inte har hämtat ut fler tallrikar än de köpt in.

Jag tackar så mycket för din hjälp och ska fundera kring om jag inte ska göra om mina tabeller :).

Gå till inlägget

Ingen fara! Det är viktigt att man lägger lite krut på databasdesignen också. Nu var ju Teknocide snäll (som han alltid är) och gjorde en fin fråga åt dig. Personligen fick jag lite ont i huvudet när man måste skriva querys på det sättet för att få ut något så enkelt (om databasstrukturen tillåter det). Men det är helt rätt väg att gå om databasstrukturen ser ut på det sättet.

Du kan göra så att de gånger du vill presenterar data från databasen där positiva och negativa tal varierar så gör du det med det absoluta värdet så är det problemet löst.

Man ökar komplexiteten när man måste räkna med andra tabeller. Inget fel med det alls, men om man kan göra det enklare och fortfarande ha en trevlig datastruktur så är det inte önskvärt med komplexitet.
I just detta fall så ser jag inga direkta problem om du skulle lägga förköpta antalen i (den för nuvarande fiktionella tabellen) "lagertillfallerad". På samma sätt här kan man sätta en typ på varje rad för att säga om det är incheck/utcheck/förköp/återköp/etc om du vill vara säker på att du räknar värden rätt vid olika tillfällen. Om du vill ha ut statistik så kanske man vill ställa specifika frågor "Hur många incheckningar har gjorts totalt?" osv.

Men du verkar förstå vad jag menar och mer än så ville jag inte säga. Om du känner att du kan styrka designvalen du gör med databasen så är det rock n roll.

Visa signatur

ηλί, ηλί, λαμά σαβαχθανί!?

Redigera
Citera flera Citera
Permalänk
Medlem
●

Vad smart, jag kan köra abs() på värdet :).

Visa signatur

Livets 3 pelare:
SEX, MAT & KAFFE.

Redigera
Citera flera Citera
Permalänk
Medlem
●

Nytt problem

Nedanstående fungerar men utelämnar den högra kolumnen när saker är annorluda i den.

SELECT `ut`.art_id, `ut`.antal, `ut`.datum, `in`.datum, `in`.antal FROM
(SELECT art_id, antal, datum FROM utcheck2 INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id WHERE for_id = 1 ORDER BY art_id) `ut`
LEFT JOIN
(SELECT art_id, antal, datum FROM incheck2 INNER JOIN incheck ON incheck.id = incheck2.incheck_id WHERE for_id = 1 ORDER BY art_id) `in`
ON `in`.datum = `ut`.datum  AND `in`.art_id = `ut`.art_id
 art_id 	antal 	datum 	datum 	antal
1 	100 	2013-01-03 	2013-01-03 	10
1 	100 	2013-01-04 	NULL	NULL
2 	200 	2013-01-03 	2013-01-03 	20
2 	200 	2013-01-04 	NULL	NULL
3 	300 	2013-01-03 	2013-01-03 	30
3 	300 	2013-01-04 	NULL	NULL
4 	400 	2013-01-03 	2013-01-03 	40
4 	400 	2013-01-04 	NULL	NULL

FULL JOIN verkar inte finnas i MySQL? Testat med UNION ALL. Men här får jag ett felmeddelande istället.
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5"

SELECT `ut`.art_id, `ut`.antal, `ut`.datum, `in`.datum, `in`.antal FROM
(SELECT art_id, antal, datum FROM utcheck2 INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id WHERE for_id = 1 ORDER BY art_id) `ut`
LEFT JOIN
(SELECT art_id, antal, datum FROM incheck2 INNER JOIN incheck ON incheck.id = incheck2.incheck_id WHERE for_id = 1 ORDER BY art_id) `in`
ON `in`.datum = `ut`.datum  AND `in`.art_id = `ut`.art_id UNION ALL
Visa signatur

Livets 3 pelare:
SEX, MAT & KAFFE.

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
Skrivet av Lurcazzus:

Nytt problem

Nedanstående fungerar men utelämnar den högra kolumnen när saker är annorluda i den.

SELECT `ut`.art_id, `ut`.antal, `ut`.datum, `in`.datum, `in`.antal FROM
(SELECT art_id, antal, datum FROM utcheck2 INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id WHERE for_id = 1 ORDER BY art_id) `ut`
LEFT JOIN
(SELECT art_id, antal, datum FROM incheck2 INNER JOIN incheck ON incheck.id = incheck2.incheck_id WHERE for_id = 1 ORDER BY art_id) `in`
ON `in`.datum = `ut`.datum  AND `in`.art_id = `ut`.art_id
 art_id 	antal 	datum 	datum 	antal
1 	100 	2013-01-03 	2013-01-03 	10
1 	100 	2013-01-04 	NULL	NULL
2 	200 	2013-01-03 	2013-01-03 	20
2 	200 	2013-01-04 	NULL	NULL
3 	300 	2013-01-03 	2013-01-03 	30
3 	300 	2013-01-04 	NULL	NULL
4 	400 	2013-01-03 	2013-01-03 	40
4 	400 	2013-01-04 	NULL	NULL

FULL JOIN verkar inte finnas i MySQL? Testat med UNION ALL. Men här får jag ett felmeddelande istället.
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5"

SELECT `ut`.art_id, `ut`.antal, `ut`.datum, `in`.datum, `in`.antal FROM
(SELECT art_id, antal, datum FROM utcheck2 INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id WHERE for_id = 1 ORDER BY art_id) `ut`
LEFT JOIN
(SELECT art_id, antal, datum FROM incheck2 INNER JOIN incheck ON incheck.id = incheck2.incheck_id WHERE for_id = 1 ORDER BY art_id) `in`
ON `in`.datum = `ut`.datum  AND `in`.art_id = `ut`.art_id UNION ALL
Dold text
Gå till inlägget

Vad är det du försöker göra?

Visa signatur

Kom-pa-TI-bilitet

Redigera
Citera flera Citera
Permalänk
Medlem
●

Jag vill presentera nedan:

 art_id 	in.antal 	in.datum 	ut.datum 	ut.antal
1 	 NULL	NULL   	2013-01-02 	11
1 	100 	2013-01-03 	2013-01-03 	10
1 	100 	2013-01-04 	NULL	NULL
2 	 NULL	NULL   	2013-01-02 	12
2 	200 	2013-01-03 	2013-01-03 	20
2 	200 	2013-01-04 	NULL	NULL
3 	 NULL	NULL   	2013-01-02 	13
3 	300 	2013-01-03 	2013-01-03 	30
3 	300 	2013-01-04 	NULL	NULL
4 	 NULL	NULL   	2013-01-02 	14
4 	400 	2013-01-03 	2013-01-03 	40
4 	400 	2013-01-04 	NULL	NULL

Detta för att kunna per artikel se vad som händer dag för dag.

Mitt problem är att den antingen utelämnar värden från den högra när jag kör en LEFT JOIN eller den vänstra när jag kör en RIGHT JOIN.

Visa signatur

Livets 3 pelare:
SEX, MAT & KAFFE.

Redigera
Citera flera Citera
Permalänk
Medlem ★
●
Skrivet av Lurcazzus:

Detta för att kunna per artikel se vad som händer dag för dag.

Mitt problem är att den antingen utelämnar värden från den högra när jag kör en LEFT JOIN eller den vänstra när jag kör en RIGHT JOIN.

Gå till inlägget

MySQL är lite eget. Läste precis att du borde kunna göra

SELECT `ut`.art_id, `ut`.antal, `ut`.datum, `in`.datum, `in`.antal FROM
(SELECT art_id, antal, datum FROM utcheck2 INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id WHERE for_id = 1 ORDER BY art_id) `ut`
LEFT JOIN
(SELECT art_id, antal, datum FROM incheck2 INNER JOIN incheck ON incheck.id = incheck2.incheck_id WHERE for_id = 1 ORDER BY art_id) `in`
ON `in`.datum = `ut`.datum  AND `in`.art_id = `ut`.art_id
UNION
SELECT `ut`.art_id, `ut`.antal, `ut`.datum, `in`.datum, `in`.antal FROM
(SELECT art_id, antal, datum FROM utcheck2 INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id WHERE for_id = 1 ORDER BY art_id) `ut`
RIGHT JOIN
(SELECT art_id, antal, datum FROM incheck2 INNER JOIN incheck ON incheck.id = incheck2.incheck_id WHERE for_id = 1 ORDER BY art_id) `in`
ON `in`.datum = `ut`.datum  AND `in`.art_id = `ut`.art_id
Visa signatur

Kom-pa-TI-bilitet

Redigera
Citera flera Citera
Permalänk
Medlem
●

Ett stort tack igen 

SELECT `ut`.art_id, `ut`.antal, `ut`.datum, `in`.datum, `in`.antal FROM
(SELECT art_id, antal, datum FROM utcheck2 INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id WHERE for_id = 1 ORDER BY art_id) `ut`
LEFT JOIN
(SELECT art_id, antal, datum FROM incheck2 INNER JOIN incheck ON incheck.id = incheck2.incheck_id WHERE for_id = 1 ORDER BY art_id) `in`
ON `in`.datum = `ut`.datum  AND `in`.art_id = `ut`.art_id
UNION
SELECT `in`.art_id, `ut`.antal, `ut`.datum, `in`.datum, `in`.antal FROM
(SELECT art_id, antal, datum FROM utcheck2 INNER JOIN utcheck ON utcheck.id = utcheck2.utcheck_id WHERE for_id = 1 ORDER BY art_id) `ut`
RIGHT JOIN
(SELECT art_id, antal, datum FROM incheck2 INNER JOIN incheck ON incheck.id = incheck2.incheck_id WHERE for_id = 1 ORDER BY art_id) `in`
ON `in`.datum = `ut`.datum  AND `in`.art_id = `ut`.art_id ORDER BY art_id

Ändrade SELECT ut till in i andra satsen och la till order by art_id.

Visa signatur

Livets 3 pelare:
SEX, MAT & KAFFE.

Redigera
Citera flera Citera
Permalänk
Medlem ★
●

Alltså herregud, javisst, jattebra att du försöker komma så nära 3NF som möjligt men är det verkligen värt det? Hade du nöjt dig med 1 tabell för in och 1 tabell för ut hade du kunnat göra samma sak med 2 selects i princip. Helt galna databasfrågor till ingen egentlig nytta.

Visa signatur

Hur kan syltkakor överleva i det vilda utan ögon?

Redigera
Citera flera Citera
1
Skriv svar
Senaste nyheterna
Hårdvara
Mjukvara
Övrigt
Nytt i forumet
Datorkomponenter
System
Ljud, bild och kommunikation
Spel och mjukvara