If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

SHIP_LINE was joined with ORDER_LINE by a condition OL.ORDER_ID = SL.ORDER_ID only.
So, each row of ORDER_LINE(i.e. partID = ABC123 , KLM444 , QRP456) might be joined with all rows of SHIP_LINE which had same ORDER_ID(i.e. Qty
= 3 , 4 , 100).

This is why we need DDL. There is no way to see how the REFERENCES are set up. We also have a DATE data type now and CURRENT_TIMESTAMP (not the old Sybase getdate()/UNIX).

I see that you also have Kabbalah numbers called “id” that violate the Law of Identity (“To be is to be something in particular; to be nothing in particular is to be nothing”). Your magical “id” can be a user (what is that?), a customer, an order, an automobile or squid! Think how silly that is.

The usual model is to make the shipper_id a reference in the Orders (assuming one shipper per order), the shipment quantity an attribute of each order item (ordered_qty, shipped_qty, backorder_qty, etc)

What you have probably done is mimic pointer chains from a non-RDBMS model of data. That does not work. Let's start with the DDL.

so ship_line.shipped_qty has Order_id's attached to the QTY numbers, causing the repitition of output.

I thought that SHIP_LINE.LINE_NO or SHIP_LINE.ORDER_LINE_NO might be corresponded to ORDER_LINE.LINE_NO.
I guessed SHIP_LINE.ORDER_LINE_NO was more likely to correspond to ORDER_LINE.LINE_NO.
If so,
try to add a condition
AND SL.ORDER_LINE_NO = OL.LINE_NO
to
INNER JOIN SHIP_LINE SL ON OL.ORDER_ID = SL.ORDER_ID

This is not DDL! This is a narrative again. Even worse, you have a magical Kabbalah number called “row_id” that is probably a psychical location in a table on one disk on one machine. Nothing you posted makes any sense in RDBMS, nothing follows ISO-11179 rules. Why do you think that “id” and “name” not vague generic attribute properties?

Identifiers are seldom INTEGER because we do not use them for calculations. But we need to have check digits and regular expressions, so we use tag numbers stored in strings. Look at your credit cards, etc

Look at that table; it has a plural name because it models a set, not oen cusoemr at a time. I picked CHAR(9) for the customer_nbr because that is a common length for identifiers such as the DUNS. The VARCHAR(35) for name is USPS standard for mailing labels. See the need for basic research? The use of industry standards?

Next table:

CREATE TABLE Users:
(p_id ??,
d_id ??);

What do “p_id” and “d_id” reference? What is a “p”? What is a “d”? You said they were integers, but show them as strings. Did the magical “id” transmogrify into a user in this table? Then it becomes an order in the following table. I will drop it until it makes sense.

Without any specs, or DDL, I will guess that an order goes out in a single shipment. If that is wrong, I can change this and create a weak entity for the parts of a shipment, then put it in a VIEW that looks like my Shipmetns table.

You then have another fundamental design error. We do not model the paper forms, so we would never model the physical lines from a paper form. We model the items in an order as a weak entity. How do you identify your items? UPC? EAN? Internal SKU? I guess part_gtin, but we do not know from the narrative. I will also use CHAR(15) because of GTIN Standards.

A shipper does not have a package attribute; he has a relationship with a package or a shipment. I am an author, and do not have books; the books and I have an authorship relation. You data model is wrong.

My guess at the desired query is a count of undelivered items by customers and parts. That could be done with something like this:

SELECT C.customer_name, OD.part_gtin,
SUM(SH.shipment_qty) AS undelivered_qty
FROM Customers AS C,
Orders AS O,
Order_Details AS OD,
Shipments AS SH
WHERE O.order_nbr = OD.order_nbr
AND O.customer_nbr = C.customer_nbr
AND O.shipment_nbr = SH.shipment_nbr
AND received_date IS NOT NULL
GROUP BY C.customer_name, OD.part_gtin;

You need more help than you can get here. Start with a book on RDBMS, then one on Data Modeling.

I bought Joe's "SQL for Smarties" in the fall of '98(iirc). My contract was to be the remediation dba for the mainframe y2k work. There was a problem with the group that was to provide SQL Server resources and i was asked to stand up 10 Win-servers, get SQL Server running, and migrate the data from the mainframe so the vendors could verify their products worked on the new servers. . . Some did, some needed lots of help.

I think that design of Users table should be reconsidered, because of the reasons Celko described...

Originally Posted by Celko

...

Next table:

CREATE TABLE Users:
(p_id ??,
d_id ??);

What do “p_id” and “d_id” reference? What is a “p”? What is a “d”? You said they were integers, but show them as strings. Did the magical “id” transmogrify into a user in this table? Then it becomes an order in the following table. I will drop it until it makes sense.