SQL query join issue - it doesn't return the needed values

Question

Post reply

SQL query join issue - it doesn't return the needed values

Bahmudales

SSC Enthusiast

Points: 129
More actions
September 1, 2018 at 4:57 am

#367703

I am struggling to finish a simple query and it seems that I will never succeed.

It's about a NAV report, I want to bring some columns from purchase order table and retrieve the item's stock from another table.

First part, is OK:

SELECT PL.[No_] as "Code", PL.[Description] as "Description", ITB.[Band Item No_] as "Band No"FROM [HIL$Purchase Line] PLJOIN [HIL$Purchase Header] PH on PL.[Document No_] = PH.[No_]LEFT JOIN [HIL$Item] IT ON PL.[No_] = IT.[No_]LEFT JOIN [HIL$Item Bands] ITB ON IT.[No_] = ITB.[Item No_]WHERE PH.[Order for Week] like 'CW34%'

Result set:
Code Description Band No_ G00000000001591 BECHEROVKA KARL.BITTER 1L 38% B00000000000015 G00000000001591 BECHEROVKA KARL.BITTER 1L 38% B00000000000015 G00000000001591 BECHEROVKA KARL.BITTER 1L 38% B00000000000015 G00000000002157 FREIX ROSE BRUT 0.75L 12% NULL

The second part is also OK:
SELECT [Item No_], CAST(SUM([Quantity]) AS REAL) AS "Stoc"FROM [HIL$ITEM LEDGER ENTRY]WHERE [Posting Date] < '2018-09-01' AND [Item No_] like 'B%'GROUP BY [Item No_]HAVING SUM([Quantity]) <> 0

Result set:

Band No_ Stock B00000000000015 2066

But when joining both pieces of code the end result is not OK meaning that I have no values on "Stock" column although they exist in the second table.

SELECT PL.[No_] as "Code", PL.[Description] as "Description", ITB.[Band Item No_] as "Band No", S.Stoc as "Stoc band" FROM [HIL$Purchase Line] PL JOIN [HIL$Purchase Header] PH on PL.[Document No_] = PH.[No_] JOIN [HIL$Item] IT ON PL.[No_] = IT.[No_] LEFT JOIN [HIL$Item Bands] ITB ON IT.[No_] = ITB.[Item No_] LEFT JOIN (SELECT [Item No_], CAST(SUM([Quantity]) AS REAL) AS Stoc FROM [HIL$ITEM LEDGER ENTRY] WHERE [Posting Date] < '2018-09-01' AND [Item No_] like 'B%' GROUP BY [Item No_] HAVING SUM([Quantity]) <> 0) S ON (S.[Item No_] = PH.[No_]) WHERE PH.[Order for Week] like 'CW34%'

Result set:

Code Description Band No_ Stock G00000000001591 BECHEROVKA KARL.BITTER 1L 38% B00000000000015 NULL G00000000001591 BECHEROVKA KARL.BITTER 1L 38% B00000000000015 NULL G00000000001591 BECHEROVKA KARL.BITTER 1L 38% B00000000000015 NULL G00000000002157 FREIX ROSE BRUT 0.75L 12% NULL NULL

What am I doing wrong?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

andycadley SSCertifiable Points: 5295 More actions · Answer 1

andycadley

SSCertifiable

Points: 5295

September 1, 2018 at 6:12 am

#2004200

You appear to be joining on the "code" column of the first query, where you should be joining on the "band no"

Bahmudales SSC Enthusiast Points: 129 More actions · Answer 2

andycadley - Saturday, September 1, 2018 6:12 AM

You appear to be joining on the "code" column of the first query, where you should be joining on the "band no"

Hi andycadley,

I did the wrong join due to the primary key of all tables which is the item code.
Based on your proposal, indeed the retrieved stock was ok.
Thanks for your input!