bom where used

This is a classic hierarchy problem.  In SQL 2005 & 2008 we got the CTE that can be recursive ... and can help with such things.

http://www.eggheadcafe.com/articles/sql_server_recursion_with_clause.asp

If that's not enough help, I'll try to write you some code!

If you could write some sample code, I would appreciate it.

Can you show the complete table structure of the Parts List table, are there columns to indicate Head Assemblies or sub-assemblies and such?  The question when you want to do recurrsion is where are you starting, you either have to pick a part and work up to the parent(s) or start with the top Assembly and work down to all the subs and parts.  I am not sure what you are wanting yet.  To "find all of the parent part numbers where the parts list exist" I read that as finding all parts that are parents and have sub-parts and you can get that with something like this:
      Select *
      From [Parts List Table]
      Where PartId in (Select ParentId From [BOM Table])
That will return all Parts that are Parents but that may include Sub-Assemblies that are components of other Parts and not necessarily stand alone.
Does that make any sense?  I am just trying to get to the root of your question.
It may also help to provide a small sample of data that either I or Daniel could use to help provide example code.
HTH,
Chris

Here are the tables with which I'm working.  Am I close?

I'm working on the CTE query ...

Create Table dbo.Parts
( PartID int)
go

Create Table dbo.BOM
( ParentID int,
	ChildPartID int)
go

Insert Into Parts(PartID) Values(1)	
Insert Into Parts(PartID) Values(2)
Insert Into Parts(PartID) Values(3)
Insert Into Parts(PartID) Values(4)
Insert Into Parts(PartID) Values(5)
Insert Into Parts(PartID) Values(6)
Insert Into Parts(PartID) Values(7)
Insert Into Parts(PartID) Values(8)
Insert Into Parts(PartID) Values(9)
Insert Into Parts(PartID) Values(10)
Insert Into Parts(PartID) Values(11)	
Insert Into Parts(PartID) Values(12)
Insert Into Parts(PartID) Values(13)
Insert Into Parts(PartID) Values(14)
Insert Into Parts(PartID) Values(15)
Insert Into Parts(PartID) Values(16)
Insert Into Parts(PartID) Values(17)
Insert Into Parts(PartID) Values(18)
Insert Into Parts(PartID) Values(19)

go

Insert Into BOM(ParentID, ChildPartID) Values (8,9)
Insert Into BOM(ParentID, ChildPartID) Values (8,7)
Insert Into BOM(ParentID, ChildPartID) Values (8,6)
Insert Into BOM(ParentID, ChildPartID) Values (8,5)
Insert Into BOM(ParentID, ChildPartID) Values (2,8)
go

Select allOpen in new window

This is what I've come up with, but it's wrong.

CG, do you know these CTE's better?

with MyCTE (Parent, Child)
AS
(
	Select P.PartID, B.ChildPartID from Parts P left Join BOM B on P.PartID = B.ParentID
		--Where B.ParentID is NULL
	UNION ALL
	Select P.PartID, B.ChildPartID from Parts P Inner Join BOM B on P.PartID = B.ParentID
	Inner Join MyCTE as C on B.ChildPartID = C.Child
)
Select * from MyCTE Where Child =9

Select allOpen in new window

so here is my actual tables with sample data, please help

tbl802_parts (table)

PART_NUM
C1710-80127      
C5683-23040      
C8264A            
CB001A      


vBOM (table)    

PARENT_PART_NUM      USAGE      SITE_ID      CHILD_PART_NUM
AS895US#ABA             1                KW944AV#ABA      
AS895US#ABA             1                GW678AV          
AS895US#ABA             1                FH032AV#ABA      
AS895US#ABA             1                FH631AV          
AS895US#ABA             1                FJ659AV#ABA      

Well, that is interesting because no PART_NUM exists in vBOM

as shown by the query :

SELECT * FROM tbl802_parts WHERE part_num in (select distinct parent_part_num from vbom union select distinct child_part_num from vbom)

Which is probably better structured as :

SELECT * FROM tbl802_parts WHERE exists (select NULL from vbom where part_num in (parent_part_num,child_part_num))

or even :

SELECT distinct tbl802_parts.part_num  
FROM tbl802_parts
LEFT JOIN vBom P on tbl802_parts.part_num = P.Parent_part_num
LEFT JOIN vBom C on tbl802_parts.part_num = C.child_part_num
WHERE p.parent_part_num is not null or c.child_part_num is not null

But as for the CTE, there doesnt seem to be the requirement if all we need to do is to check the existance of part_num in the vBom. Maybe you could clarify ?

Thanks for jumping in on this, Mark.  The CTE was giving me fits!

No worries Daniel, but me thinks it might not be jist a CTE problem, beginning to think it is "show me parts that are used (anywhere) in bom", or maybe the BOM has entries that no longer exists, either way, I think there is more to it...

And while I think about it, we can also find parts used in BOM via :

SELECT distinct tbl802_parts.part_num  
FROM tbl802_parts
INNER JOIN vBom P on tbl802_parts.part_num in (P.Parent_part_num,P.child_part_num)