How to compare a column value with multiple values from another column in the same table in SQL

Question

I need to compare column user_id with another column order_id in the same table Orders.

If a user_id has multiple distinct order_id's, then I need to default order_id to '00000'. If user_id has two same order_id's, then that user_id should have only one entry.

Input table: Orders

user_id	order_id
A234	87655
A234	89765
A234	98723
R678	09874
R678	09874

Expected output:

user_id	order_id
A234	00000
R678	09874

Use GROUP BY user_id and HAVING COUNT(*) > 1

Barmar
– Barmar

2021-10-05 18:48:16 +00:00
Commented Oct 5, 2021 at 18:48 — Barmar
– Barmar, Commented Oct 5, 2021 at 18:48
Tag only the database that you use.

forpas
– forpas

2021-10-05 18:50:38 +00:00
Commented Oct 5, 2021 at 18:50 — forpas
– forpas, Commented Oct 5, 2021 at 18:50

Yogesh Sharma · Accepted Answer · 2021-10-05 18:55:56Z

2

Simple aggregation would work with case expression :

select user_id, 
       case when min(order_id) <> max(order_id) then 00000 else min(order_id) end as order_id
from orders 
group by user_id;

answered Oct 5, 2021 at 18:55

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

How to compare a column value with multiple values from another column in the same table in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related