Skip to Main Content

SQL & PL/SQL

Do not count duplicate values in SQL

BjornlaatDec 22 2022 — edited Dec 22 2022

Good morning,

I am having some problems with my current query, namely that duplicate values are being counted. This is, because in a pair table of two tables, values can appear twice.

The current query is as follows:

SELECT
BUY.SHIPMENT_XID BUYSHIPMENT,
SELL.SHIPMENT_XID SELLSHIPMENT
FROM
SHIPMENT SELL,
V30_SHIPMENT_ORDER_RELEASE V30,
V30_SHIPMENT_ORDER_RELEASE V300,
SHIPMENT BUY
WHERE
SELL.PERSPECTIVE = 'S
AND SELL.SHIPMENT_GID = V30.SHIPMENT_GID
AND V30.ORDER_RELEASE_GID = V300.ORDER_RELEASE_GID
AND V300.PERSPECTIVE = 'B
AND V300.SHIPMENT_GID = BUY.SHIPMENT_GID

In the table shipment there is only the shipment number as shipment_gid and perspective indicating whether it is a buy shipment or a sellshipment via B or S
Example data:
SHIPMENT_GID, PERSPECTIVE
1, B
2, S
3, B
4, S

To link a buy shipment to a sellshipment, oracle has the table: V30_SHIPMENT_ORDER_RELEASE. Here 3 fields are available: PERSPECTIVE, SHIPMENT_GID and ORDER_RELEASE_GID. By means of an ORDER_RELEASE_GID (or several) buy shipments are linked to a sellshipment.
Example data:
SHIPMENT_GID, PERSPECTIVE, ORDER_RELEASE_GID
1, B, RELEASE1
2, S, RELEASE1
3, B, RELEASE2
3, B, RELEASE3
4, S, RELEASE2
4, S, RELEASE3

the result the above query should give is:
BUYSHIPMENT, SELLSHIPMENT
1, 2
3,4

the current query returns the following result
BUYSHIPMENT, SELLSHIPMENT
1, 2
3,4
3,4

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 19 2023
Added on Dec 22 2022
4 comments
122 views