Do not count duplicate values in SQL

Question

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

Do not use the old version of joining tables by using where. Use the modern joinsyntax — Jens
– Jens, Commented Dec 22, 2022 at 8:16
Why GROUP BY, @Jens, and not DISTINCT? I'd use GROUP BY if there were aggregates in the query (and there are none). DISTINCT seems to be a more natural choice. But yes, result would be the same. — Littlefoot
– Littlefoot, Commented Dec 22, 2022 at 8:18

p3consulting · Accepted Answer · 2022-12-22 08:54:18Z

2

select DISTINCT b.SHIPMENT_GID as BUYSHIPMENT, s.SHIPMENT_GID as SELLSHIPMENT
from V30_SHIPMENT_ORDER_RELEASE b
join V30_SHIPMENT_ORDER_RELEASE s on s.PERSPECTIVE = 'S' and b.PERSPECTIVE= 'B' and s.ORDER_RELEASE_GID = b.ORDER_RELEASE_GID

(Assuming you have no orphan rows in the V30_SHIPMENT_ORDER_RELEASE table).

If you need other columns of SHIPMENT you can put it in a subquery and JOIN after the distinct pairs have been calculate.

answered Dec 22, 2022 at 8:54

p3consulting

5,4212 gold badges14 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bjorn.delaat Over a year ago

Solution above is not working :(

p3consulting Over a year ago

sqlfiddle.com/#!4/ed1dd/1

Collectives™ on Stack Overflow

Do not count duplicate values in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related