CODEWITHSUNDEEP
sqlsql-serverjoin
Tesherista
Tesherista

Reputation: 23

SQL query to find records with specific prefix

I'm writing SQL queries and getting tripped up by wanting to solve everything with loops instead of set operations. For example, here's two tables (lists, really - one column each); idPrefix is a subset of idFull. I want to select every full ID that has a prefix I'm interested in; that is, every row in idFull which has a corresponding entry in idPrefix. 

idPrefix.ID     idFull.ID
----------      ---------- 
    12              8
    15              12
    300             12-1-1
                    12-1-2
                    15
                    15-1
                    300

Desired result would be everything in idFull except the value 8. Super-easy with a for each loop, but I'm just not conceptualizing it as a set operation. I've tried a few variations on the below; everything seems to return all of one table. I'm not sure if my issue is with how I'm doing joins, or how I'm using LIKE.

SELECT f.ID 
FROM idPrefix AS p
JOIN idFull AS f
ON f.ID LIKE (p.ID + '%')

Details:

Upvotes: 2

Views: 2724

Answers (1)

Danny
Danny

Reputation: 1759

You can join the full table to the prefix table with a LIKE

SELECT idFull.ID
   FROM idFull full
   INNER JOIN idPrefix pre ON full.ID LIKE pre.ID + '%'

Upvotes: 3

Related Questions