-3

I'm an experienced SWE, just new to SQL and would love a walkthrough on how to solve this particular problem.

I have an input table of name pairs. I need to determine if each pair of names are nicknames of each other or not:

left right desired
john johnathon True
jon john True
john amy False
etc... etc.. etc...

To do this, I have at my disposal a lookup table that links together canonical names and nicknames:

canonical_name nickname
johnathon jon
johnathon john
john jon
etc... etc..

What SQL query would get me there? Something with JOINs and IN statements I'm guessing, but I haven't gotten anything to work. I'm using DuckDB, but I'm guessing the solution is going to be general enough that the dialect of SQL shouldn't matter? Thank you!

Nick Crews
  • 837
  • 10
  • 13

1 Answers1

0

Assigning the names 'input' and 'lookup' to your tables, this should work:

SELECT 
i.left, 
i.right,
--See join comments
(l1.nickname IS NOT NULL OR l2.canonical_name IS NOT NULL) AS Desired

FROM
input AS i
--Assume left input is a canonical name. 
--Left join to lookups to get a column with nicknames if match is found, else null
LEFT JOIN lookup AS l1 ON i.left=l1.canonical_name
--As above but the other way around
LEFT JOIN lookup AS l2 ON i.left=l2.nickname

This answer is formatted for SSMS but the idea should be similar elsewhere.

Edit: Another answer that discards duplicates.

SELECT *
FROM input
WHERE
(left  IN (select nickname from lookup where canonical_name=right)) OR
(right IN (select nickname from lookup where canonical_name=left))
dc-ddfe
  • 487
  • 1
  • 11
  • Thanks for the help @dc-ddfe! This doesn't work because the result will have many more rows than the input. This is because the lookup table has many duplicate entries for a single canonical_name or nickname (see in my example how jon and jonathon appear multiple times) – Nick Crews Nov 21 '22 at 00:34
  • Fair point. Is the edited version closer to what you need? – dc-ddfe Nov 21 '22 at 01:05