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.