CODEWITHSUNDEEP
sqlhivehql
Someguywhocodes
Someguywhocodes

Reputation: 781

Finding data where the value is the same in two columns from the same table

So I have a simple table that holds items we offered to a customer and the items the user actually used, per day. 

date         |  offered_name  |  used_name  |  h_id
----------------------------------------------------------
2019-06-20   | Obsidian       |  Obsidian   |  100
2019-06-20   | Obsidian       |  Limestone  |  101
2019-06-20   | Limestone      |  Sandstone  |  102
2019-06-21   | Obsidian       |  Limestone  |  100
2019-06-21   | Obsidian       |  Sandtone   |  101
2019-06-21   | Limestone      |  Limestone  |  102

I want to find all the instances where the offered item matches the used item. The user can change their used_item, so I only care if they have ever matched the offered_name at least once. If they have never matched then I don’t want to select them. Output for the above would look like:

h_id  | used_offered_item_at_least_once
---------------------------------------
100   | 1 
101   | 0 
102   | 1

Similar to this question SQL - find all instances where two columns are the same but I want to compare two different columns rather than checking just the one.

Upvotes: 1

Views: 253

Answers (3)

Rohit
Rohit

Reputation: 2152

You can use group by and having:

select h_id, count(1) "used_offered_item_at_least_once" from your_table
where offered_name  = used_name
group by h_id
having count(1) = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I would write this using a case expression:

select id,
       max(case when offered_name = used_name then 1 else 0 end) as used_offered_item_at_least_once
from t
group by id;

I cannot think of a simpler way to express the logic.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

You can use conditional aggregation

select h_id,
       cast(sign(sum(case when offered_name = used_name then 
          1 
       else
          0
       end)) as int) as used_offered_item_at_least_once
  from tab
 group by h_id

Upvotes: 1

Related Questions