ali javadi ali javadi - 4 months ago 4
SQL Question

how to join multiple tables without showing repeated data?

I pop into a problem recently, and Im sure its because of how I Join them.
this is my code:

select LP_Pending_Info.Service_Order,
LP_Pending_Info.Pending_Days,
LP_Pending_Info.Service_Type,
LP_Pending_Info.ASC_Code,
LP_Pending_Info.Model,
LP_Pending_Info.IN_OUT_WTY,
LP_Part_Codes.PartCode,
LP_PS_Codes.PS,
LP_Confirmation_Codes.SO_NO,
LP_Pending_Info.Engineer_Code
from LP_Pending_Info

join LP_Part_Codes
on LP_Pending_Info.Service_order = LP_Part_Codes.Service_order

join LP_PS_Codes
on LP_Pending_Info.Service_Order = LP_PS_Codes.Service_Order

join LP_Confirmation_Codes
on LP_Pending_Info.Service_Order = LP_Confirmation_Codes.Service_Order

order by LP_Pending_Info.Service_order, LP_Part_Codes.PartCode;


enter image description here

For every service order I have 5 part code maximum.

If the service order have only one value it show the result correctly but when it have more than one Part code the problem begin.



for example: this service order"4182134076" has only 2 part code, first'GH81-13601A' and second 'GH96-09938A' so it should show the data 2 time but it repeat it for 8 time. what seems to be the problem?

Answer

If your records were exactly the same the distinct keyword would have solved it.

However in rows 2 and 3 which have the same Service_Order and Part_Code if you check the SO_NO you see it is different - that is why distinct won't work here - the rows are not identical.

I say you have some problem in one of the conditions in your joins. The different data is in the SO_NO column so check the raw data in the LP_Confirmation_Codes table for that Service_Order:

select * from LP_Confirmation_Codes where Service_Order = 4182134076

I assume you are missing an and with the value from the LP_Part_Codes or LP_PS_Codes (but can't be sure without seeing those tables and data myself).

By this sentence If the service order have only one value it show the result correctly but when it have more than one Part code the problem begin. - probably you are missing and and with the LP_Part_Codes table

Comments