CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2008-r2
mOrloff
mOrloff

Reputation: 2617

SQL Server: Retrieve rows where the Prod Num is like a string found in a different table?

GOAL:
Our goal is to retrieve a list of all sales activity for any PN that begins with any targeted prod series.
In the SqlFiddle found here, this would return rows 1 through 4 from the Sales table.
The targeted product series are currently on a spreadsheet, which I'll probably just import it into a temp table or something to simplify querying.

OVERVIEW:
Here's a simplification of our sales history table:

CREATE TABLE Sales ([id] int, [pn] varchar(9), [qty] int, [price] int);

Feel free to fiddle.

Let's presume we have a product series called ABC for which there are multiple counts, such as ABC-100ct and ABC-200ct.
The spreadsheet only contians series numbers, such as ABC.

If it was a one-off, I would simply query where CONTAINS or LIKE, but there are around 155 series being targeted.

QUESTION:
How do I query for rows where the PN begins with a string found in any row in a different table?

[EDIT]
BTW, our series numbers are of various lengths. I realize that the sample provided didn't really convey that, but they vary considerably.
[/EDIT]

Upvotes: 1

Views: 40

Answers (2)

mellamokb
mellamokb

Reputation: 56779

You can join on substrings if you can come up with a function to get the product series reliably out of the product code. For example, if it is always the first three characters:

SELECT *
FROM Sales
JOIN temp on left(Sales.pn, 3) = temp.pn

Demo: http://sqlfiddle.com/#!3/11502/3

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

If I'm understanding your question correctly, you can use like in your join criteria:

SELECT *
FROM Sales S
  JOIN temp t ON s.pn like t.pn + '%'

Upvotes: 2

Related Questions