Calling table with same key but...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calling table with same key but...

Author

Topic

clinderman
Starting Member

1 Post

Posted - 2011-06-17 : 12:04:04

Let's say I have 2 tables: dbo.part_details and dbo.bom.

dbo.part_details has Part Number, Part Name and Part Cost.

dbo.bom has what parts go on what assemblies.

Bear in mind that assemblies will also be in dbo.part_details.

So my Tables may look like this:

dbo.part_detail

Part_Number - Part_Name - Part_Cost

557 - PartA - 15.02
543 - PartB - 2.33
771 - PartZ - 75.25

dbo.bom

Part_Number - Part_Reports_to

543 - 557
771 - 557

So, you can tell that the Part_Number is the key between these 2. Now, I want my query to show me the Part Number, the name of the part it reports to and the cost. So I want SQL to pull the Part Name and replace the Part Reports to. For example:

Part Name - Reports to - Part Cost

PartB - PartA - 2.33
PartN - PartA - 5.24

The problem I am getting stuck on, and I don't know if I need to use some sort of crazy join is my Query won't do that. It does this instead:

Part Name - Reports to - Part Cost

PartB - 557 - 2.33
PartN - 557 - 5.24

How do I get around that? Please advise.

Thank you.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-17 : 13:34:20

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

>> Let's say I have 2 tables: dbo.part_details and dbo.bom. <<

That's nice! Want to share with the rest of us? I would use a nested sets model for the BOM. From yo0ur vague narrative, you used an adjacency list model. Starting to see all the problems bad DDL makes for you?

CREATE TABLE Parts
(part_nbr INTEGER NOT NULL PRIMARY KEY,
part_name VARCHAR(15) NOT NULL,
part_cost DECIMAL (8,2) NOT NULL);

INSERT INTO Parts
VALUES
(557, ' PartA', 15.02),
(543, 'partB', 2.33),
(771, 'PartZ', 75.25);

CREATE TABLE Assemblies
(part_nbr INTEGER NOT NULL
REFERENCES Parts(part_nbr)
ON UPDATE CASCADE,
assembly_qty INTEGER NOT NULL, –--how many are used in this assembly?
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt));

INSERT INTO Assemblies
VALUES
(557, 1, 1, 6),
(543, 1, 2, 3),
(771, 1, 4, 5);

>> Bear in mind that assemblies will also be in dbo.part_details. <<

That is part of your bad DDL problems

>> So, you can tell that the Part_Number is the key between these 2.<<

No, the part number is a key in one and only table; it is a references in other tables. Important concept. Here are the basic manipulations for the Nested Sets model:

1. An assembly and all their containing assemblies, no matter how deep the tree.

SELECT A2.*
FROM Assemblies AS A1, Assemblies AS A2
WHERE A1.lft BETWEEN A2.lft AND A2.rgt
AND A1.part_nbr = @my_part_nbr

2. The assembly and all their subordinates. There is a nice symmetry here.

SELECT A1.*
FROM Assemblies AS A1, Assemblies AS A2
WHERE A1.lft BETWEEN A2.lft AND A2.rgt
AND A2.part_nbr = @my_part_nbr;

3. Add a GROUP BY and aggregate functions to these basic queries and you have hierarchical reports. For example, the total salaries which each assembly controls:

SELECT A2.part_nbr, SUM(S1.part_cost)
FROM Assemblies AS A1, Assemblies AS A2
WHERE A1.lft BETWEEN A2.lft AND A2.rgt
GROUP BY A2.part_nbr;

4. To find the level of each part_nbr, so you can print the tree as an indented listing.

SELECT T1.node,
SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END
+ CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END) AS lvl
FROM Tree AS T1, Tree AS T2
WHERE T2.lft <= T1.lft
GROUP BY T1.node;

5. To convert a nested sets model into an adjacency list model:

SELECT B.part_nbr AS boss_part_nbr, E.part_nbr
FROM Assemblies AS E
LEFT OUTER JOIN
Assemblies AS B
ON B.lft
= (SELECT MAX(lft)
FROM Assemblies AS S
WHERE E.lft > S.lft
AND E.lft < S.rgt);

6. To find the immediate parent of a node:

SELECT MAX(P2.lft), MIN(P2.rgt)
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.part_nbr = :my_part_nbr;

I have a book on TREES & HIERARCHIES IN SQL which you can get at Amazon.com right now.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources