İki veri Tablosunu Bir Kart Tablosuna Nasıl Bağlarım

[harunkomus]

İki Satır Veri Tablosunu Bir Kart Tablosuna Nasıl Bağlarım

Sipariş Hareketleri Tablosu
Stok Harekteleri Tablosu
Stok Kartları

üç tane tablo

Stok Kartına diğer tabloları bağlıyarak miktar ve tutar olarak rapor almak istiyorum.
Stok Kartlarının tamamı listelenecek hareket tablolarındaki veriler toplanacak.
SELECT KOMUTUNU Örnek gösterebilirmisiniz.




SİPARİŞ HAREKETLERİ ve STOK HAREKETLERİ listesi aşağıdaki gibi
Bu iki SELECT komutunu TEK satırda listeliyecek şekilde birleştirmek istiyorum.

SELECT
LG_030_ITEMS.CODE ,
LG_030_ITEMS.NAME ,
'SATIŞ' = SUM(CASE when LG_030_01_STLINE.TRCODE=3 then -LG_030_01_STLINE.AMOUNT When LG_030_01_STLINE.TRCODE=8 then LG_030_01_STLINE.AMOUNT ELSE NULL END),
'1 : GİRİŞ' = SUM(CASE LG_030_01_STLINE.IOCODE when 1 then LG_030_01_STLINE.AMOUNT ELSE NULL END),
'2 : Ambar giriş' =SUM(CASE LG_030_01_STLINE.IOCODE when 2 then LG_030_01_STLINE.AMOUNT ELSE NULL END),
'3 : Ambar çıkış'=SUM(CASE LG_030_01_STLINE.IOCODE when 3 then LG_030_01_STLINE.AMOUNT ELSE NULL END),
'4 : Çıkış'=SUM(CASE LG_030_01_STLINE.IOCODE when 4 then LG_030_01_STLINE.AMOUNT ELSE NULL END),
'STOK KALAN' = SUM(CASE WHEN LG_030_01_STLINE.IOCODE IN(1,2) then LG_030_01_STLINE.AMOUNT when LG_030_01_STLINE.IOCODE IN(3,4) then -LG_030_01_STLINE.AMOUNT ELSE NULL END),
'MONTE' = SUM (CASE WHEN LG_030_01_STLINE.TRCODE=13 AND (LG_030_01_STLINE.DATE_>={ts '2010-01-01 00:00:00'}) THEN LG_030_01_STLINE.AMOUNT ELSE NULL END),
'MONTE ADET' = SUM (CASE WHEN LG_030_01_STLINE.TRCODE=13 AND (LG_030_01_STLINE.DATE_>={ts '2010-01-01 00:00:00'}) AND LG_030_ITEMS.UNITSETREF = 9 THEN LG_030_01_STLINE.AMOUNT*2
WHEN LG_030_01_STLINE.TRCODE=13 AND (LG_030_01_STLINE.DATE_>={ts '2010-01-01 00:00:00'}) AND LG_030_ITEMS.UNITSETREF = 5 THEN LG_030_01_STLINE.AMOUNT ELSE NULL END)
FROM
LOGO.dbo.LG_030_01_STLINE LG_030_01_STLINE,
LOGO.dbo.LG_030_ITEMS LG_030_ITEMS
WHERE
LG_030_01_STLINE.STOCKREF = LG_030_ITEMS.LOGICALREF AND
((LG_030_ITEMS.CARDTYPE=12) AND (LG_030_ITEMS.STGRPCODE Like 'K%'))
GROUP BY
LG_030_ITEMS.CODE ,
LG_030_ITEMS.NAME
ORDER BY
LG_030_ITEMS.CODE


SELECT
LG_030_ITEMS.CODE ,
LG_030_ITEMS.NAME ,

'SİPARİŞ' = SUM(CASE LG_030_01_ORFLINE.STATUS WHEN 4 THEN LG_030_01_ORFLINE.AMOUNT ELSE NULL END),
'SEVK EDİLEN'=SUM(LG_030_01_ORFLINE.SHIPPEDAMOUNT),
'KALAN'=SUM(CASE WHEN (LG_030_01_ORFLINE.CLOSED=0) AND (LG_030_01_ORFLINE.STATUS=4)THEN (LG_030_01_ORFLINE.AMOUNT-LG_030_01_ORFLINE.SHIPPEDAMOUNT)ELSE NULL END),
'ONAYLANACAK' = SUM(CASE LG_030_01_ORFLINE.STATUS WHEN 1 THEN (LG_030_01_ORFLINE.AMOUNT-LG_030_01_ORFLINE.SHIPPEDAMOUNT)ELSE NULL END),
'SEVK EDİLEMEZ'=SUM(CASE LG_030_01_ORFLINE.STATUS WHEN 2 THEN (LG_030_01_ORFLINE.AMOUNT-LG_030_01_ORFLINE.SHIPPEDAMOUNT)ELSE NULL END),
'İPTAL'= SUM(CASE WHEN (LG_030_01_ORFLINE.CLOSED=1) AND (LG_030_01_ORFLINE.STATUS=4) AND (LG_030_01_ORFLINE.SHIPPEDAMOUNT=0) THEN (LG_030_01_ORFLINE.AMOUNT-LG_030_01_ORFLINE.SHIPPEDAMOUNT)ELSE NULL END),
'KİSMEN İPTAL'=SUM(CASE WHEN (LG_030_01_ORFLINE.CLOSED=1)AND(LG_030_01_ORFLINE.STATUS=4) AND (LG_030_01_ORFLINE.SHIPPEDAMOUNT>0)THEN (LG_030_01_ORFLINE.AMOUNT-LG_030_01_ORFLINE.SHIPPEDAMOUNT)ELSE NULL END),
'GENEL İPTAL' = SUM(CASE WHEN (LG_030_01_ORFLINE.CLOSED=1) AND (LG_030_01_ORFLINE.STATUS=4)THEN (LG_030_01_ORFLINE.AMOUNT-LG_030_01_ORFLINE.SHIPPEDAMOUNT)ELSE NULL END)
FROM
LOGO.dbo.LG_030_01_ORFLINE LG_030_01_ORFLINE,
LOGO.dbo.LG_030_ITEMS LG_030_ITEMS
WHERE
LG_030_01_ORFLINE.STOCKREF = LG_030_ITEMS.LOGICALREF
AND ((LG_030_01_ORFLINE.TRCODE=1) AND (LG_030_ITEMS.CARDTYPE=12) AND (LG_030_ITEMS.STGRPCODE Like 'K%'))
GROUP BY
LG_030_ITEMS.CODE ,
LG_030_ITEMS.NAME
ORDER BY
LG_030_ITEMS.CODE