问题描述:

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;

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?

网友答案:

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

网友答案:

Based on your output result, here are the following data that caused multiple output.

Service Order: 4182134076 has :

2 PartCode which are GH81-13601A and GH96-09938A

2 PS which are U and P

2 SO_NO which are 1.00024e+09 and 1.00022e+09

Therefore 2^3 returns 8 rows. I believe that you need to check where you should join your tables.

网友答案:

Use DINTINCT

select distinct 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_Part_Codes.Service_Order = LP_PS_Codes.Service_Order
 join LP_Confirmation_Codes on LP_PS_Codes.Service_Order = LP_Confirmation_Codes.Service_Order
order by LP_Pending_Info.Service_order, LP_Part_Codes.PartCode;

distinct will not return duplicates based on your select. So if a row is same, it will only return once.

相关阅读:
Top