0

I have an issue in joining multiple table. When I add another join to my correct query quantity gets messed up. I joined the status table because I want to get the status from there. (Using SSMS)

This is the correct code and the actual correct quantity:

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID =         IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH
HAVING
  SUM(TIT.QTY) > 0

Correct code executed with correct quantity showing
Correct code executed with correct quantity showing

Now when I try to join another table, I still want to get the same quantity shown above.

Here's what I tried but the quantity seems to have been multiplied by 9 or something. It could also be that my joins are wrong or I have a silly mistake.

Here's the new code (Joining the Part_Location table) but my quantity is incorrect (QTY should be the same as shown in image 1)

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  L.STATUS,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID =     IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
INNER JOIN PART_LOCATION L ON L.PART_ID = TIT.PART_ID 
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
  AND L.STATUS = 'A'
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  L.STATUS
HAVING
  SUM(TIT.QTY) > 0

Alteration made where lines 6, 17, 21, and 32 where added but now QTY is incorrect
Alteration made where lines 6, 17, 21, and 32 where added but now QTY is incorrect

Shuvo
  • 75
  • 6
SQL
  • 21
  • 5
  • Most likely this is because a single part can be in more than one location. So when you join to that table you are going to get rows for all the locations. – Sean Lange Jun 15 '22 at 14:57
  • 1
    Tag spam doesn't help us help you; it makes it harder to. Tagging multiple conflicting tags means that we have no idea what technology you are really asking about, making your question unclear and difficult to answer. Tag spam can also end up attracting downvotes and also close votes if they make the question unclear. Just tag the technologies you are actually asking about. I have removed the conflicting tags, though those images look like SSMS; implying SQL Server. – Thom A Jun 15 '22 at 14:57
  • Try to remove the JOIN on locations and to use an EXISTS or an IN clause instead for the condition about your location/status. – Jonas Metzler Jun 15 '22 at 14:59
  • Apologies @SeanLange, I'm using SSMS. Let me know if you can't see the pictures – SQL Jun 15 '22 at 15:02
  • Not very clear on how I can do that? I still need to bring in the Part_Location table since it contains the STATUS field @JonasMetzler – SQL Jun 15 '22 at 15:09
  • Then you probably need to add the STATUS to your group by. – Sean Lange Jun 15 '22 at 15:14
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) [ask] [Help] – philipxy Jun 16 '22 at 09:20
  • Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For wrong results that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL include DDL & tabular initialization code. When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. – philipxy Jun 16 '22 at 09:21
  • Does this answer your question? [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/questions/12464037/two-sql-left-joins-produce-incorrect-result) – philipxy Jun 16 '22 at 09:23

1 Answers1

2

You can use subquery instead of join. If you have more than one status for any L.PART_ID then the query will show error. you need to change the subquery according to your dbms.

For sql server:

select top 1 STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A'

for MySql:

select STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A' Limit 1

Query:

SELECT
  TIT.PART_ID,
  TIT.TRACE_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  (select STATUS from PART_LOCATION L where L.PART_ID = TIT.PART_ID and L.STATUS = 'A'),
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH,
  SUM(TIT.QTY) AS QTY
FROM
TRACE_INV_TRANS TIT --TIT = TRACE INV TRANS TABLE
INNER JOIN INVENTORY_TRANS IT ON TIT.PART_ID = IT.PART_ID AND TIT.TRANSACTION_ID =         IT.TRANSACTION_ID
LEFT JOIN PART_SITE P ON P.PART_ID = TIT.PART_ID
WHERE
  IT.LOCATION_ID = 'DISPATCH'
  AND TIT.QTY IS NOT NULL
GROUP BY
  TIT.TRACE_ID,
  TIT.PART_ID,
  IT.WAREHOUSE_ID,
  IT.LOCATION_ID,
  P.PRIMARY_WHS_ID,
  P.PRIMARY_LOC_ID,
  P.BACKFLUSH_WHS_ID,
  P.BACKFLUSH_LOC_ID,
  P.AUTO_BACKFLUSH
HAVING
  SUM(TIT.QTY) > 0
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26