Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MSSQL Query help

Posted on 2013-12-23
14
Medium Priority
?
267 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 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