|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-15 : 08:09:20
|
Emi writes "Hi, I have a situation where my Departments table contains parent ids. The
departments table relates to a products table. I need to be able to run a proc that takes input for the Department. The result should return the productname and the departmentname, however if the run on a parent department or super parent department (mind you parents and superparents have no records in the products table)... i need to get all of the child records and their corresponding products. If they enter a child, I need only products for that child. All code is included in this email.
Here is the beginnings of the stored proc. It is the where clause that I
need.
Create proc Parent_Child @Department varchar(50) as
SELECT Products.ProductName,
Departments.DEPARTMENT_NAME
FROM Departments INNER JOIN
Products ON Departments.ID = Products.DepartmentID
--where... this is the tricky part.
Here is how the Departments data looks:
ID Department ParentID
1 All Games Null
2 Baby Games 7
3 Toddler Games 7
4 Teen Games 7
5 All Clothing Null
6 Adult Games 1
7 Kids Games 1
Here is how the Products data looks:
ID Product Department_ID
1 Bubble Master 2
2 Counting Sticks 3
3 Barbie Makeup 4
4 Adult Shirt 5
5 Teen Shirt 5
6 Toddler Shirt 5
7 Calculus Quest 6
8 Trivia of the Reformation 6
Here is the script to create and populate tables.
if exists (select * from sysobjects where id =
object_id(N'[dbo].[Departments]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Departments]
GO
if exists (select * from sysobjects where id =
object_id(N'[dbo].[Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Products]
GO
CREATE TABLE [dbo].[Departments] (
[ID] [int] NOT NULL ,
[DEPARTMENT_NAME] [varchar] (50) NULL ,
[Parent_ID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Products] (
[ID] [int] NOT NULL ,
[ProductName] [varchar] (50) NULL ,
[DepartmentID] [int] NULL
) ON [PRIMARY]
GO
Insert into Departments
Values
(1,'All Games',Null)
Insert into Departments
Values
(2,'Baby Games',7)
Insert into Departments
Values
(3,'Toddler Games',7)
Insert into Departments
Values
(4,'Teen Games',7)
Insert into Departments
Values
(5,'All Clothing',Null)
Insert into Departments
Values
(6,'Adult Games',1)
Insert into Departments
Values
(7,'Kids Games',1)
Insert into Products
Values
(1,'Bubble Master',2)
Insert into Products
Values
(2,'Counting Sticks',3)
Insert into Products
Values
(3,'Barbie Makeup',4)
Insert into Products
Values
(4,'Adult Shirt',5)
Insert into Products
Values
(5,'Teen Shirt',5)
Insert into Products
Values
(6,'Toddler Shirt',5)
Insert into Products
Values
(7,'Calculus Quest',6)
Insert into Products
Values
(8,'Trivia of the Reformation',6)
Thank you all so very much,
Saba :)
" |
|
Parent Child Queries... Very Tough