Devam Ediyor

correct SQL JOIN

This is the SQL-Query:

select my011_LAG_Mandant,my011_LAG_MandantOrdner,my011_LAG_Artikelnummer,my011_LAG_Inventurmenge,

my011_LAG_res19,my011_LAG_Inventurdatum, my011_LAG_Inventurmenge+sum_bewegung_total

from my011_lag_lagerdatei lag

inner join my204_tmpfp_formpos

on my204_TMPFP_Mandant=my011_LAG_Mandant and my204_TMPFP_MandantOrdner=my011_LAG_MandantOrdner and

my204_TMPFP_Artikelnr=my011_LAG_Artikelnummer

join ( select slbp.my022_LBP_ID_Lagerartikel, my022_LBP_Datum, SUM(slbp.my022_LBP_Bewegung) AS sum_bewegung_total

from my022_lbp_lagerbewegungprot slbp

group by slbp.my022_LBP_ID_Lagerartikel

) lbp

on lbp.my022_LBP_ID_Lagerartikel=lag.my011_LAG_ID

Description:

lag (my011_LAG) is the full product table

my204_TMPFP is a subset of pruducts, where users put in only these products, which should calculated, so its a filter

lbp is a table with all stock movements from products

goal is, that << SUM(slbp.my022_LBP_Bewegung) AS sum_bewegung_total >> in JOIN ... should calculate a total sum of stock movements of the linked product. This works fine with the given syntax.

Now comes the problem:

Total sum should calculated only from movements after a specific stocktaking date, which is stored in my011_LAG_Inventurdatum in lag.

So I tried this with a where clause in JOIN:

join ( select slbp.my022_LBP_ID_Lagerartikel, my022_LBP_Datum, SUM(slbp.my022_LBP_Bewegung) AS sum_bewegung_total

from my022_lbp_lagerbewegungprot slbp

where my022_LBP_Datum>lag.my011_LAG_Inventurdatum

group by slbp.my022_LBP_ID_Lagerartikel

) lbp

on lbp.my022_LBP_ID_Lagerartikel=lag.my011_LAG_ID

but this isn't working and I need it solved. It gives the error "lag.my011_LAG_Inventurdatum unknown" whithin the where clause.

Beceriler: MySQL

Daha fazlasını görün: mysql join table, mysql select filter, mysql table join, sql syntax error, works join, mysql sql syntax, sql syntax, date clause, stocktaking mysql, join group, sql query sum, subset problem, stock sql, subset sum problem, need sql query, sum subset problem, sum subset, subset sum, unknown clause, sql query filter, sql query date, query sql full, full sql query, sql query working, stocktaking

İşveren Hakkında:
( 43 değerlendirme ) Sankt Andrae, Austria

Proje NO: #6555778

Seçilen:

yuriyvsl

Hello, SQL is my passion. I'm working in top 10 retail in russia. so I have an experience in SQL The query is ready Regards, Yuriy

1 gün içinde 15$ USD
(7 Değerlendirme)
3.1

3 freelancer bu iş için ortalamada 35$ teklif veriyor

sat12

Hi, I will fix the issue of join query. I am joomla, virtuemart expert having 5 years of experience... recently i completed these projects http://www.ticotogo.com http://www.uniquestylefashion.com/ http://www.msc Daha fazlası

1 gün içinde 34$ USD
(49 Değerlendirme)
4.9
jinsongtong

Hello sir. I checked your description carefully. I am a db expert. I know solution. Because i met same thing before 3 months ago. So this is no problem for me. My previous links. http://www.youroneca Daha fazlası

in 0 gün içinde55$ USD
(0 Değerlendirme)
0.0