?
Solved

MSSQL Query help

Posted on 2013-12-23
14
Medium Priority
?
262 Views
Last Modified: 2014-01-05
Hello there,

I have this query which is returning some redundant data. Can somebody please help me to correct this query. I have also attached the table relationship.

SELECT
    dbo.supplierOrder.sectionID,
    dbo.supplierOrder.supplierID,
    dbo.supplierOrder.supplierOrderNo,
    dbo.supplierOrder.supplierOrderCreated,
    dbo.supplierOrder.supplierOrderConfirmStatus,
    dbo.supplierOrderDetails.productID,
    dbo.supplierOrderDetails.orderQty,
    dbo.supplierOrderReceive.invoiceno,
    dbo.supplierOrderReceive.supplierInvoiceno,
    dbo.supplierOrderReceive.ID,
    dbo.supplierOrderReceiveDetail.qtyArrived
FROM
    dbo.supplierOrder
INNER JOIN
    dbo.supplierOrderDetails
ON
    (
        dbo.supplierOrderDetails.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
        dbo.supplierOrderReceive
ON  
     (        
       dbo.supplierOrderReceive.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
        dbo.supplierOrderReceiveDetail
ON  
     (        
       dbo.supplierOrderReceiveDetail.supplierOrderReceiveID = dbo.supplierOrderReceive.ID)       
WHERE
    dbo.supplierOrder.ID = 1 ;

Open in new window

table-relationship.png
table-relation.png
0
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
  • 2
  • +2
14 Comments
 

Author Comment

by:zolf
ID: 39737401
here is the output that I get

which is not exactly what I was want. the last column qtyArrived should show only its related qty but here as you can see it is repeating for each of the productID

sectionID      supplierID      supplierOrderNo      supplierOrderCreated      supplierOrderConfirmStatus      productID      orderQty      invoiceno      supplierInvoiceno      ID      qtyArrived
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      8
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      8
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      223444      1      8
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      565333      2      1
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      565333      2      1
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      565333      2      1
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      565333      2      4
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      565333      2      4
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      565333      2      4
0
 
LVL 7

Expert Comment

by:Phil Davidson
ID: 39737455
I would use the keyword DISTINCT before
dbo.supplierOrderReceiveDetail.qtyArrived

Open in new window

0
 

Author Comment

by:zolf
ID: 39737458
thanks for your comment.can you please give me the full query.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 7

Expert Comment

by:Phil Davidson
ID: 39737459
SELECT
    dbo.supplierOrder.sectionID,
    dbo.supplierOrder.supplierID,
    dbo.supplierOrder.supplierOrderNo,
    dbo.supplierOrder.supplierOrderCreated,
    dbo.supplierOrder.supplierOrderConfirmStatus,
    dbo.supplierOrderDetails.productID,
    dbo.supplierOrderDetails.orderQty,
    dbo.supplierOrderReceive.invoiceno,
    dbo.supplierOrderReceive.supplierInvoiceno,
    dbo.supplierOrderReceive.ID,
   DISTINCT dbo.supplierOrderReceiveDetail.qtyArrived
FROM
    dbo.supplierOrder
INNER JOIN
    dbo.supplierOrderDetails
ON
    (
        dbo.supplierOrderDetails.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
        dbo.supplierOrderReceive
ON  
     (        
       dbo.supplierOrderReceive.supplierOrderID = dbo.supplierOrder.ID)
INNER JOIN 
        dbo.supplierOrderReceiveDetail
ON  
     (        
       dbo.supplierOrderReceiveDetail.supplierOrderReceiveID = dbo.supplierOrderReceive.ID)       
WHERE
    dbo.supplierOrder.ID = 1 ;

Open in new window

0
 

Author Comment

by:zolf
ID: 39737462
no this give me error

 Incorrect syntax near the keyword 'DISTINCT'.

I tried to add DISTINCT at the start and it ran the query but does not give me the desired result
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737496
I just verified the data you have provided , I am not able to get complete record duplication here
 ID and       qtyArrived
is differenct for all cloumns
0
 

Author Comment

by:zolf
ID: 39737521
what do you mean. i have printed my result,it is duplicating the data. for e.g. for all the productID it is repeating the same qtyArrived. my final result should return only 4 records.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39737537
sectionID      supplierID      supplierOrderNo      supplierOrderCreated      supplierOrderConfirmStatus      productID      orderQty      invoiceno      supplierInvoiceno      ID      qtyArrived
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      223444      1      2

you mean to say you need one record for this ? what will be that
0
 

Author Comment

by:zolf
ID: 39737545
i am expecting this

sectionID      supplierID      supplierOrderNo      supplierOrderCreated      supplierOrderConfirmStatus      productID      orderQty      invoiceno      supplierInvoiceno      ID      qtyArrived
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      8
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      565333      2      1
1      1      (null)      2013-12-10 12:35:03      true      6      4      (null)      565333      2      4
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39738751
Can you explain how you would derive those rows from the data you posted earlier?  I am not seeing any rhyme or reason.  Take that first row:
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      8

I assume it comes from the following rows:
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      5      8      (null)      223444      1      8

Is it the"last" one that you are looking for?  If so, can you define the "last" one?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39738755
For the second row:
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2

I am assuming it is coming from:
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      2
1      1      (null)      2013-12-10 12:35:03      true      1      3      (null)      223444      1      8

But here you appear to have picked the "first" one.  

Perhaps it does not matter which one is returned.
0
 

Author Comment

by:zolf
ID: 39738869
thanks for your comments.
see the invoice numbers in the result.  the idea is to get the products which the supplier has sent to the client. the products could have come in one batch or in small batches. the productid 1 has come in 2 batches(the orderQty by the client was 3 but it qtyArrived in 2 batches of 1 and 2). see the attached file.
table-relationship.png
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39752347
You are missing an additional JOIN condition on dbo.supplierOrderReceiveDetail.productID = dbo.supplierOrderDetails.productID
For better readability, I have used table aliases. see this query.
SELECT SO.sectionID, 
       SO.supplierID, 
       SO.supplierOrderNo, 
       SO.supplierOrderCreated, 
       SO.supplierOrderConfirmStatus, 
       SOD.productID, 
       SOD.orderQty, 
       SOR.invoiceno, 
       SOR.supplierInvoiceno, 
       SOR.ID, 
       SORD.qtyArrived 
  FROM dbo.supplierOrder SO 
       INNER JOIN dbo.supplierOrderDetails SOD 
               ON SOD.supplierOrderID = SO.ID 
       INNER JOIN dbo.supplierOrderReceive SOR 
               ON SOR.supplierOrderID = SO.ID 
       INNER JOIN dbo.supplierOrderReceiveDetail SORD 
               ON SORD.supplierOrderReceiveID = SOR.ID 
                  AND SORD.productID = SOD.productID 
 WHERE SO.ID = 1; 

Open in new window

http://sqlfiddle.com/#!3/6cdf5/1
0
 

Author Closing Comment

by:zolf
ID: 39758505
thanks
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question