Get unique data pair from table join

user_13336507 0
Viewed 38

I'm trying to make a matching program in SQL where it output the following data

  • Shared street
  • Shared pet
  • Person 1 favorite food
  • Person 2 favorite food

The People table have a distinct timestamp column

I wrote

Create Table match As
  Select a.street, a.pet, a.food, b.food
  From People a, People b
  Where a.street = b.street And a.pet = b.pet And a.time <> b.time
  Order by a.time;

Example table

Street Pet Food Time
maple dog pizza 2021/07/28 12:55:56 PM MDT
maple cat pizza 2021/07/28 3:34:56 PM MDT
maple dog french fries 2021/07/28 12:34:56 PM MDT
abney dog pizza 2021/07/28 5:34:56 PM MDT
abney cat pizza 2021/07/28 12:45:56 PM MDT
abney dog french fries 2021/07/28 2:34:56 PM MDT

For example it outputs this

maple|dog|pizza|french fries
maple|dog|french fries|pizza (duplicate)
abney|dog|pizza|french fries
abney|dog|french fries|pizza (duplicate)

How can I make it so it doesn't return duplicate value?

Edit: I might not want to use "Distinct" since there are a lot of non-duplicate values that can be flagged as duplicate (since its the same output but from different people)

duplicates join sqlite
1 Answers

Instead of the operator <> use > (or <) to compare the times.
Also use a proper join with an ON clause:

SELECT p1.street, p1.pet, p1.food, p2.food
FROM People p1 INNER JOIN People p2
ON p2.street = p1.street AND p2.pet = p1.pet AND p2.time > p1.time
ORDER BY p1.time;

See the demo.

user_104988280
Related