April 30, 2010 at 2:21 am
Hi, I'm a problem I had .5 relationship between different tables. I need to add more to this relationship a table. Thus, the relationship between the table I will have six. In the first table, and sixth in the table in question is not data. The name of the sixth LG_086_PRICE table. I was in the first table, the sixth in the table do not want to come to the data value is NULL. I'm very weak in joins. Thanks to what might help.
Code with the following six statements, incorrect number brings the data. In this case there are errors in the code join. But I do not know the solution.
SELECT SIPFIS.DATE_ AS Tarih, SIPFIS.TIME_ AS Saat, SIPFIS.FICHENO AS Sipariş_No, STOK.PRODUCERCODE AS Barkodu, STOK.CODE AS [Stok Kodu],
STOK.NAME AS [Stok Adı], SIPSATIR.AMOUNT AS Miktar, LG_086_01_GNTOTST.ONHAND AS [Eldeki Miktar], SIPSATIR.PRICE AS [SATIŞ BR.Fiyat],
LG_086_PRCLIST.PRICE AS [Liste Fiyatı], SIPSATIR.VATMATRAH / SIPSATIR.AMOUNT AS [NET SATIŞ BR.Fiyat], SIPSATIR.TOTAL AS [Br?t.Tutar],
SIPSATIR.VATMATRAH AS [Net Tutar],
(SELECT TOP 1 OUTREMCOST
FROM LG_086_01_STLINE
WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR
TRCODE = 8 OR
TRCODE = 1) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0
ORDER BY DATE_ DESC) AS BrMaliyet, SIPSATIR.AMOUNT *
(SELECT TOP 1 OUTREMCOST
FROM LG_086_01_STLINE
WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR
TRCODE = 8 OR
TRCODE = 1) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0
ORDER BY DATE_ DESC) AS [Toplam Maliyet], SIPSATIR.VATMATRAH - SIPSATIR.AMOUNT *
(SELECT TOP 1 OUTREMCOST
FROM LG_086_01_STLINE
WHERE STOCKREF = STOK.LOGICALREF AND (TRCODE = 51 OR
TRCODE = 8 OR
TRCODE = 1) AND OUTREMCOST <> 0 AND LPRODSTAT = 0 AND LINETYPE = 0
ORDER BY DATE_ DESC) AS NETKAR
FROM
LG_086_ITEMS STOK INNER JOIN
LG_086_01_ORFLINE SIPSATIR ON STOK.LOGICALREF = SIPSATIR.STOCKREF INNER JOIN
LG_086_PRCLIST ON STOK.LOGICALREF = LG_086_PRCLIST.CARDREF RIGHT OUTER JOIN
LG_086_01_ORFICHE SIPFIS ON SIPSATIR.ORDFICHEREF = SIPFIS.LOGICALREF LEFT OUTER JOIN
LG_086_01_GNTOTST ON SIPSATIR.STOCKREF = LG_086_01_GNTOTST.STOCKREF
WHERE (SIPFIS.FICHENO = '00076166') AND (LG_086_01_GNTOTST.INVENNO = 0) AND (LG_086_PRCLIST.PTYPE = 2)
Ersoy Aydın System Specialist LBS.
Thanks for your help.
April 30, 2010 at 12:11 pm
I'm afraid I couldn't make out enough of that to understand the question, but I will note that having criteria in your WHERE clause targeting SIPFIS and LG_086_01_GNTOTST turns those outer joins into inner joins.
May 1, 2010 at 1:37 am
First of all, thank you for your interest.
These complex queries, an order of profitability analysis. A diagram will now add a picture.
Then the table names, field names and will send a diagram describing the relationship.
Table field names in some funny, some are in English acronym, some of them are Turkish shortened formed. We'll send you a chart, will eliminate all the complexity.
6 store card in order to have the chips.Table name LG_086_ITEMS
Price List price information in the table has only four stock cards.Table name LG_086_PRICE
I write my SQL code returns the following result.
NamePrice
ITEM1Price1
ITEM2Price2
ITEM3Price3
ITEM6Price6
However, I want to return must be based on the results as follows.
NamePrice
ITEM1Price1
ITEM2Price2
ITEM3Price3
ITEM4NULL
ITEM5NULL
ITEM6Price6
http://www.sqlservercentral.com/Forums/Topic913514-392-1.aspx#bm913778
Thank you.
May 1, 2010 at 10:02 am
Right, I figured as much, which is why I mentioned the where clause and the outer joins. YOu need to take the criteria out of your where clause and put it in the ON clause of the join. In the above query,
(LG_086_PRCLIST.PTYPE = 2) probably needs to move.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply