Sql request return wrong result

Question

I have issue with a SQL request or my database structure. I would like to have supplier/customer database. As the ordered price products and sold products can change, I'd like to keep an historic on each order / sales to create in the future statistics on them. I'm at the beginning of my project and using 6 simple tables :

    Client       |     OrderC       |   OrderDetail
-----------------+------------------+--------------------------------------
Client_ID (Pk)   |   Order_ID (Pk)  |   OrderD_ID (Pk)
Name             |   Client_ID (Fk) |   Order_ID (Fk)
                 |   date           |   Product_ID (Fk)
                 |                  |   Qty
                 |                  |   PU_Vte (sales price)
                 |                  | For_Cmd_ID (Fk from For_ID-Cmd table Forever_cmd)
_________________|__________________|_____________________________________


                |(supplier database)| (detail of supplier order)
   Product      |    Forever_cmd    |  For_Ord_Detail
----------------+-------------------+----------------------------------------------------
Product_ID (Pk) | For_ID_CMD (Pk)   |  For_Det_Id (Pk)
Name            |      date         |ID_cmd_For (Fk from For_ID-CMD on Forever_cmd table)
                |                   |Product_ID (Fk source Product_ID on Product table)
                |                   |   Qte (= quantity)
                |                   |  PUHA (= supplier price)

All is working fine until I have similar products on different supplier orders. The result of my sql request create extra result for the customers.

Example:

I create 2 supplier orders (S1 and S2) containing same product ID (P1) with different supplier prices. I create a customer order and I choose to sale product P1 from S1.

When I query to obtain a view by client with products and to track them from supplier order, the result add all similar products from all supplier orders to the customers. Instead of only the products ordered by the customers.

I don't know if it is from my query or database consistency.

Here is my sql request :

SELECT C.Name,
       O.order_id,
       F.For_ID_CMD,
       P.Name,
       D.Qte,
       D.PU_Vte,
       X.PUHA
FROM Client AS C
     JOIN OrderC O ON C.Client_ID = O.Client_ID
     JOIN OrderDetail D ON O.Order_ID = D.Order_ID
     JOIN For_Ord_Detail X ON D.Product_ID = X.Product_ID
     JOIN Forever_Cmd F ON F.For_ID_CMD = X.ID_cmd_For
     JOIN Product P ON D.Product_ID = P.Product_ID;

Could someone please help me ?

Answer 1

First, a quick code review:

1. "date" is a special word. I'd recommend not using it as a column header. Be a bit more descriptive about the type of date these are. OrderC_date and Forever_cmd_date. Or OC_date, FC_date.

2. You've used both "Qty" and "Qte" for quantity. Be consistent. Or even better, be more descriptive of what those quantities are.

For your duplicates, remove your JOINs one at a time until you figure out which one is causing the dupes. I'm betting you need to narrow your JOIN criteria on one of those tables.

===========================================================

EDIT

This should point you in the right direction to find your duplicates.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Product ( Product_ID int, Name varchar(20) ) ;
INSERT INTO Product (Product_ID, Name)
VALUES 
    (1, 'Widget1')
  , (2, 'Widget2')
;

CREATE TABLE Forever_cmd ( For_ID_CMD int, [date] date ) ;
INSERT INTO Forever_cmd ( For_ID_Cmd, [date] ) /* Why a date? */
VALUES (1,'10/26/1985'), (2,'6/27/2012');

CREATE TABLE For_Ord_Detail ( For_Det_Id int, ID_cmd_For int, Product_ID int, Qte int, PUHA decimal(10,2) ) ;
INSERT INTO For_Ord_Detail ( For_Det_Id, ID_cmd_For, Product_ID, Qte, PUHA )
VALUES 
    (1,1,1,10,2.00)
  , (2,1,2,10,12.00)
  , (3,2,2,20,20.00)
;

Query 1:

SELECT F.For_ID_CMD, P.Name,  X.PUHA
FROM Product P
LEFT OUTER JOIN For_Ord_Detail X ON P.Product_ID = X.Product_ID  <<<<<
LEFT OUTER JOIN Forever_Cmd F ON X.ID_cmd_For = F.For_ID_CMD

Results:

| For_ID_CMD |    Name | PUHA |
|------------|---------|------|
|          1 | Widget1 |    2 |
|          1 | Widget2 |   12 |  << Why did this "duplicate"?
|          2 | Widget2 |   20 |  << Why did this "duplicate"?

Hint: How do you determine which supplier you get your product from if multiple suppliers have the same product?