Generating the missing link (SQL Problem)

[jwcoleman87]

Okay, so two queries, side by side here:

select PartNumber, ProductType, TTPartInventory.Location, TTPartLocation.LocationID from TTPartInventory
join SKU on TTPartInventory.PartNumber = SKU.ManufacturerPart
join TTPartLocation on TTPartLocation.Location = TTPartInventory.Location

Select PartType, PartClassification From TTPartTypeClass
Join TTPartClass on TTPartTypeClass.PartClassID = TTPartClass.PartClassID
Join TTPartType on TTPartTypeClass.PartTypeID = TTPartType.PartTypeID

Keep in mind SKU is just a giant SKU table which describes a part in a very very complete manner. Those two queries bring up information that looks like this:

The first statement:

PartNumber ProductType Location LocationID
501552-001           DOOR                 A01 1
531215-001           CIRCUIT BOARD        A01 1
60.M8EN2.007         CASES                A01 1
607750-001           COVER                A01 1
620594-001           BEZELS               A01 1

The second statement:

PartType PartClassification
BEZELS               Large Plastics      
BASES                Large Plastics      
CASES                Large Plastics      
COVER                Large Plastics      
HARDWARE             Hardware and stuff  
BRACKETS             Hardware and stuff  
SPEAKER              Hardware and stuff  
DOOR                 Hardware and stuff  
FAN                  Hardware and stuff  
HEATSINK             Hardware and stuff

All this information is coming from a database that looks like this:


What I'd like to do is create records for the locations that are found having a certain product type in them and select the correct partclassification for the table TTLocationClass

I want to simply ask, is your producttype listed here? if it is then your partclassid will be x

I only need a single partclassID per location.

 

[jmcilhinney]

So basically you're saying that you want to be able to do this:

SELECT TTPartLocation.Location, TTPartClass.PartClassId
FROM ...
WHERE TTPartType.PartTypeID = @PartTypeID

and you want to know what to put in the FROM clause, correct?

 

[jwcoleman87]

yeahhhhhhhh! I'm having trouble figuring that part out!
because there is no data in the TTLocationClass table, yet....

I -could- manually enter the 59 records I need, but I'm thinking there may be a better way.

 

[jwcoleman87]

this got me what I needed, was a pain to figure out!

use FastrackData
Select distinct TTPartLocation.Location, TTPartClass.PartClassID
From SKU
Join TTPartInventory on TTPartInventory.PartNumber = SKU.ManufacturerPart
Join TTPartLocation on TTPartInventory.Location = TTPartLocation.Location
Join TTPartType on SKU.ProductType = TTPartType.PartType
join TTPartTypeClass on TTPartType.PartTypeID = TTPartTypeClass.PartTypeID
Join TTPartClass on TTPartClass.PartClassID = TTPartTypeClass.PartClassID
group by TTPartLocation.Location, TTPartClass.PartClassID
having count(TTPartInventory.PartNumber) > 1
order by TTPartLocation.Location