Creating a SQL statement that uses data in another table for criteria

In SQL Server 2012, is there a way to write a SQL Statement that will filter records in one table using data from another table as the criteria?  Here's the example:

Parts Table
ManufacturerId   PartNumber
34                           XYZ
78                           123QRS
78                           456TT

CriteriaData Table
ManufacturerId   WildcardData
34                           XYZ
34                           WWW
78                           12

I want any record in the Parts Table that has a
ManufacturerId of 34 and Part Number that starts with "XYZ"
or ManufacturerId of 34 and Part Number that starts with "WWW"
or ManufacturerId of 78 and Part Number that starts with "12"

In this example the first two records in the Parts table would be output.  Note: There are much more records in the real CriteriaData table, and I want a way to do this without dynamic SQL if possible.  Is there a way to join the PartNumber field with the WildcardData field in a way that there would be a match as long as the first part of the PartNumber matches the WildcardData?

Thanks.
LVL 1
Declan_BasileITAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT P.*
FROM dbo.Parts P
INNER JOIN dbo.CriteriaData CD ON P.ManufacturerId = CD.ManufacturerId AND P.PartNumber LIKE CD.WildcardData + '%'
0
 
Jan LouwerensSoftware EngineerCommented:
It will certainly not be an efficient query, but something like this could work for you:

SELECT Parts.ManufacturerId, Parts.PartNumber FROM Parts, CriteriaData WHERE
   Parts.ManufacturerId = CriteriaData.ManufacturerId AND
   Parts.PartNumber LIKE CriteriaData.WildcardData + '%'

Open in new window


Note that I have used "+" as the string concatenation operator. Depending on your database, it might be a different operator.
0
 
Mark WillsTopic AdvisorCommented:
First up, thank you for your well laid out question. Complete with examples and requirements.

There are a few ways to achieve your goals, and you are using the right words with
Is there a way to join the PartNumber field with the WildcardData field in a way that there would be a match as long as the first part of the PartNumber matches the WildcardData?
Both Scott and Jan have the wild card matching using "PartNumber LIKE WildcardData + '%'" which is the correct way

The only real difference is if you need to report Parts even if there isnt an entry in the CriteriaData, or using CriteriaData to find Parts.

And the reason why I raise that is your example of ManufacturerId of 34 and Part Number that starts with "WWW" (and the lead in comments).

The implications are multi-faceted :
1) There isnt any parts that qualify
2) There are potentially multiple criteria for each Part
3) Are you wanting to show Parts with matching Criteria
4) Are you wanting to use Criteria to help choose Parts

So, depending on the above, there are a number of possibilities
-- Using Parts to find Criteria
-- Pretty much the same as other Answers

Select  P.ManufacturerId,P.PartNumber,C.WildcardData
from #Parts P
inner join #CriteriaData C on C.ManufacturerId = P.ManufacturerId   -- could use AND instead of Where.
where P.PartNumber like C.WildcardData + '%'


-- Report on  Parts with any (if any) Criteria

Select  P.ManufacturerId,P.PartNumber,isnull(C.WildcardData,'No Criteria Found') as WildcardData
from #Parts P
left outer join #CriteriaData C on C.ManufacturerId = P.ManufacturerId and P.PartNumber like C.WildcardData + '%'


-- Use Criteria to find Parts

Select  C.ManufacturerId,P.PartNumber,C.WildcardData
from #CriteriaData C
inner join #Parts P on C.ManufacturerId = P.ManufacturerId
where P.PartNumber like C.WildcardData + '%'

-- or

Select  C.ManufacturerId,isnull(P.PartNumber,'No Matching Parts') as PartNumber ,C.WildcardData
from #CriteriaData C
left outer join #Parts P on C.ManufacturerId = P.ManufacturerId and P.PartNumber like C.WildcardData + '%'


-- Now, the Multi Criteria for a Part

Select  P.ManufacturerId,P.PartNumber,isnull(C.WildcardData,'No Criteria Found') as WildcardData
from #Parts P
inner join #CriteriaData C on C.ManufacturerId = P.ManufacturerId and P.PartNumber like C.WildcardData + '%'

-- or

Select  C.ManufacturerId,isnull(P.PartNumber,'No Matching Parts') as PartNumber,C.WildcardData
from #CriteriaData C
inner join #Parts P on C.ManufacturerId = P.ManufacturerId and P.PartNumber like C.WildcardData + '%'


-- The big challenge with Multi-Criteria is
-- you will return the same part number for as many criteria rows that match
-- So, might need to think about grouping results for a part number...

Open in new window

0
 
Declan_BasileITAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.