Solved

MSSQL Query help

Posted on 2013-12-23
14
248 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
  • 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
 
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 40

Accepted Solution

by:
Sharath earned 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL query 4 29
Auditing with Temporal Tables 4 17
Azure SQL DB? 3 16
SQL Date Retrival 7 28
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now