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
 distinct clause not working

Author  Topic 

mirza21
Starting Member

11 Posts
Posted - 2009-10-20 : 12:06:59
this is my query . Problem is when i run this query i get duplicate productid even if I am using distinct clause.Please help to create tbale ihave provided script also
select distinct p.productid,sc.categoryid,p.name,p.description,p.price from product as p join 
(select pc.categoryid,pc.productid  from productcategory as pc join (select d.departmentid,
c.categoryid from department as d join category as c on d.departmentid= c.departmentid where 
d.departmentid = 2 )as dc on pc.categoryid = dc.categoryid ) as sc on p.productid = sc.productid 
order by p.productid




go
CREATE TABLE [dbo].[Department](
	[DepartmentID] [int] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED,
	[Name] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](1000) NULL,
 )

go
USE BalloonShop

GO

INSERT INTO Department(Name, Description)		
VALUES ('Anniversary Balloons', 'These sweet balloons are the perfect gift for someone you love.')

GO

INSERT INTO Department(Name, Description )		
VALUES ('Balloons for Children', 'The colorful and funny balloons will make any child smile!')

GO




-----------------------------------------------------------------

GO

CREATE TABLE Product(
	ProductID INT IDENTITY(1,1) NOT NULL,
	Name NVARCHAR(50) NOT NULL,
	Description NVARCHAR(MAX) NOT NULL,
	Price MONEY NOT NULL,
	Thumbnail NVARCHAR(50) NULL,
	Image NVARCHAR(50) NULL,
	PromoFront BIT NOT NULL,
	PromoDept BIT NOT NULL,
 CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID ASC)
) 


GO

TRUNCATE TABLE Product
GO

SET IDENTITY_INSERT Product ON
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (1, 'I Love You (Simon Elvin)', 'An adorable romantic balloon by Simon Elvin. You''ll fall in love with the cute bear bearing a bouquet of roses, a heart with I Love You, and a card.', 121.9900, 't0326801.jpg', '0326801.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (2, 'Elvis Hunka Burning Love', 'A heart shaped balloon with the great Elvis on it and the words "You''re My Hunka Hunka Burnin'' Love!". Also a copy of the Kings Signature.', 12.9900, 't16110p.jpg', '16110p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (3, 'Funny Love', 'A red heart-shaped balloon with "I love you" written on a white heart surrounded by cute little hearts and flowers.', 12.9900, 't16162p.jpg', '16162p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (4, 'Today, Tomorrow & Forever', 'White heart-shaped balloon with the words "Today, Tomorrow and Forever" surrounded with red hearts of varying shapes. "I Love You" appears at the bottom in a red heart.', 12.9900, 't16363p.jpg', '16363p.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (5, 'Smiley Heart Red Balloon', 'Red heart-shaped balloon with a smiley face. Perfect for saying I Love You!', 12.9900, 't16744p.jpg', '16744p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (6, 'Love 24 Karat', 'A red heart-shaped balloon with "I Love You" in script writing. Gold heart outlines adorn the background.', 12.9900, 't16756p.jpg', '16756p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (7, 'Smiley Kiss Red Balloon', 'Red heart-shaped balloon with a smiley face and three kisses. A perfect gift for Valentine''s Day!', 12.9900, 't16864p.jpg', '16864p.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (8, 'Love You Hearts', 'A balloon with a simple message of love. What can be more romantic?', 12.9900, 't16967p.jpg', '16967p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (9, 'Love Me Tender', 'A heart-shaped balloon with a picture of the King himself-Elvis Presley. This must-have for any Elvis fan has "Love Me Tender" written on it with a copy of Elvis''s signature.', 12.9900, 't16973p.jpg', '16973p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (10, 'I Can''t Get Enough of You Baby', 'When you just can''t get enough of someone, this Austin Powers style balloon says it all.', 12.9900, 't16974p.jpg', '16974p.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (11, 'Picture Perfect Love Swing', 'A red heart-shaped balloon with a cute picture of two children kissing on a swing.', 12.9900, 't16980p.jpg', '16980p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (12, 'I Love You Roses', 'A white heart-shaped balloon has "I Love You" written on it and is beautifully decorated with two flowers, a small red heart in the middle, and miniature hearts all around.', 12.9900, 't214006p.jpg', '214006p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (13, 'I Love You Script', 'A romantic red heart-shaped balloon with "I Love You" in white. What more can you say?', 12.9900, 't214041p.jpg', '214041p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (14, 'Love Rose', 'A white heart-shaped balloon with a rose and the words "I Love You." Romantic and irresistible.', 12.9900, 't214168p.jpg', '214168p.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (15, 'You''re So Special', 'Tell someone how special he or she is with this lovely heart-shaped balloon with a cute bear holding a flower.', 12.9900, 't215302p.jpg', '215302p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (16, 'I Love You Red Flourishes', 'A simple but romantic red heart-shaped balloon with "I Love You" in large script writing.', 12.9900, 't22849b.jpg', '22849b.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (17, 'I Love You Script', 'A simple, romantic red heart-shaped balloon with "I Love You" in small script writing.', 12.9900, 't45093.jpg', '45093.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (18, 'Love Cascade Hearts', 'A romantic red heart-shaped balloon with hearts and I "Love You."', 12.9900, 't68841b.jpg', '68841b.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (19, 'You''re So Special', 'Someone special in your life? Let them know by sending this "You''re So Special" balloon!', 12.9900, 't7004801.jpg', '7004801.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (20, 'Love Script', 'Romance is in the air with this red heart-shaped balloon. Perfect for the love of your life.', 12.9900, 't7008501.jpg', '7008501.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (21, 'Baby Hi Little Angel', 'Baby Hi Little Angel', 12.9900, 't115343p.jpg', '115343p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (22, 'I''m Younger Than You', 'Roses are red, violets are blue, but this balloon isn''t a romantic balloon at all. Have a laugh, and tease someone older.', 12.9900, 't16118p.jpg', '16118p.jpg', 1, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (23, 'Birthday Balloon', 'Great Birthday Balloons. Available in pink or blue. One side says "Happy Birthday To You" and the other side says  "Birthday Girl" on the Pink Balloon and "Birthday Boy" on the Blue Balloon. Especially great for children''s parties.', 12.9900, 't26013.jpg', '26013.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (24, 'Birthday Star Balloon', 'Send a birthday message with this delightful star-shaped balloon and make someone''s day!', 12.9900, 't35732.jpg', '35732.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (25, 'Tweety Stars', 'A cute Tweety bird on a blue heart-shaped balloon with stars. Sylvester is in the background, plotting away as usual.', 12.9900, 't0276001.jpg', '0276001.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (26, 'You''re Special', 'An unusual heart-shaped balloon with the words "You''re special.".', 12.9900, 't0704901.jpg', '0704901.jpg', 1, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (27, 'I''m Sorry (Simon Elvin) Balloon', 'The perfect way to say you''re sorry. Send a thought with this cute bear  balloon.', 12.9900, 't0707401.jpg', '0707401.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (28, 'World''s Greatest Mom', 'A lovely way to tell your Mom that she''s special. Surprise her with this lovely balloon on her doorstep.', 12.9900, 't114103p.jpg', '114103p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (29, 'Good Luck', 'Big day ahead? Wish someone "Good Luck" with this colorful balloon!', 12.9900, 't114118p.jpg', '114118p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (30, 'Big Congratulations Balloon', 'Does someone deserve a special pat on the back? This balloon is a perfect way to pass on the message', 12.9900, 't114208p.jpg', '114208p.jpg', 1, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (31, 'You''re So Special', 'A purple balloon with the simple words "You''re so Special!" on it. Go on, let them know they are special.', 12.9900, 't16148p.jpg', '16148p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (32, 'Thinking of You', 'A round balloon just screaming out "Thinking of You!"; especially great if you are far away from someone you care for.', 12.9900, 't16151p.jpg', '16151p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (33, 'Welcome Back', 'A great way to say Welcome Back!', 12.9900, 't16558p.jpg', '16558p.jpg', 1, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (34, 'Words of Thanks', 'A round balloon with lots and lots of Thank You''s written on it. You''re sure to get the message through with this grateful balloon.', 12.9900, 't16772p.jpg', '16772p.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (35, 'Missed You''ll Be', 'If someone special is Going away, let this cute puppy balloon tell them they''ll be missed.', 12.9900, 't16809p.jpg', '16809p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (36, 'You''re Appreciated', 'A spotty balloon with the words "You''re Appreciated". I bet they''ll appreciate it too!', 12.9900, 't16988p.jpg', '16988p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (37, 'Thinking of You', 'Thinking of someone? Let them know with this thoughtful heart-shaped balloon with flowers in the background.', 12.9900, 't214046p.jpg', '214046p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (38, 'Get Well-Daisy Smiles', 'We all get sick sometimes and need something to cheer us up. Make the world brighter for someone with this Get Well Soon balloon.', 12.9900, 't21825b.jpg', '21825b.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (39, 'Toy Story', 'Woody and Buzz from Toy Story, on a round balloon.', 12.9900, 't0366101.jpg', '0366101.jpg', 1, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (40, 'Rugrats Tommy & Chucky', 'If you are a Rugrats fan, you''ll be nuts about this purple Rugrats balloon featuring Chucky and Tommy. A definite Nickelodeon Toon favorite.', 12.9900, 't03944l.jpg', '03944l.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (41, 'Rugrats & Reptar Character', 'Rugrats balloon featuring Angelica, Chucky, Tommy, and Reptar.', 12.9900, 't03945L.jpg', '03945L.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (42, 'Tweety & Sylvester', 'A blue round balloon with the great cartoon pair: Tweety & Sylvester.', 12.9900, 't0510801.jpg', '0510801.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (43, 'Mickey Close-up', 'A close-up of Mickey Mouse on a blue heart-shaped balloon. Check out our close-up matching Minnie balloon.', 12.9900, 't0521201.jpg', '0521201.jpg', 1, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (44, 'Minnie Close-up', 'A close-up of Minnie Mouse on a pink heart-shaped balloon. Check out our close-up matching Mickey balloon.', 12.9900, 't0522101.jpg', '0522101.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (45, 'Teletubbies Time', 'Time for Teletubbies balloon. Great gift for any kid.', 12.9900, 't0611401.jpg', '0611401.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (46, 'Barbie My Special Things', 'Barbie and her friends on a round balloon.', 12.9900, 't0661701.jpg', '0661701.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (47, 'Paddington Bear', 'Remember Paddington? A must-have for any Paddington Bear lover.', 12.9900, 't215017p.jpg', '215017p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (48, 'I Love You Snoopy', 'The one and only Snoopy hugging Charlie Brown to say "I Love You."', 12.9900, 't215402p.jpg', '215402p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (49, 'Pooh Adult', 'An adorable Winnie the Pooh balloon.', 12.9900, 't81947pl.jpg', '81947pl.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (50, 'Pokemon Character', 'A Pokemon balloon with a lot of mini pictures of the rest of the cast. Pokemon, Gotta catch ''em all!', 12.9900, 't83947.jpg', '83947.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (51, 'Pokemon Ash & Pikachu', 'A Pokemon balloon with Ash and Pikachu. Gotta catch ''em all!', 12.9900, 't83951.jpg', '83951.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (52, 'Smiley Kiss Yellow', 'The ever-famous Smiley Face balloon on the classic yellow background with three smooch kisses.', 12.9900, 't16862p.jpg', '16862p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (53, 'Smiley Face', 'A red heart-shaped balloon with a cartoon smiley face.', 12.9900, 't214154p.jpg', '214154p.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (54, 'Soccer Shape', 'A soccer-shaped balloon great for any soccer fan.', 12.9900, 't28734.jpg', '28734.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (55, 'Goal Ball', 'A round soccer balloon. Ideal for any sports fan, or an original way to celebrate an important Goal in that "oh so important" game.', 12.9900, 'ta1180401.jpg', 'a1180401.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (56, 'Wedding Doves', 'A white heart-shaped balloon with wedding wishes and intricate designs of doves in silver.', 12.9900, 't1368601.jpg', '1368601.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (57, 'Crystal Rose Silver', 'A transparent heart-shaped balloon with silver roses. Perfect for a silver anniversary or a wedding with a silver theme.', 12.9900, 't38196.jpg', '38196.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (58, 'Crystal Rose Gold', 'A transparent heart-shaped balloon with Gold roses. Perfect for a Golden anniversary or a wedding with a Gold theme.', 12.9900, 't38199.jpg', '38199.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (59, 'Crystal Rose Red', 'A transparent heart-shaped balloon with red roses. Perfect for an anniversary or a wedding with a red theme.', 12.9900, 't38202.jpg', '38202.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (60, 'Crystal Etched Hearts', 'A transparent heart-shaped balloon with silver hearts. Perfect for a silver anniversary or a wedding with a silver theme.', 12.9900, 't42014.jpg', '42014.jpg', 0, 1)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (61, 'Crystal Love Doves Silver', 'A transparent heart-shaped balloon with two love doves in silver.', 12.9900, 't42080.jpg', '42080.jpg', 0, 0)
GO

INSERT INTO Product(ProductID, Name, Description, Price, Thumbnail, Image, PromoFront, PromoDept )		
VALUES (62, 'Crystal Etched Hearts', 'A transparent heart-shaped balloon with red hearts.', 12.9900, 't42139.jpg', '42139.jpg', 0, 0)
GO

SET IDENTITY_INSERT Product OFF
GO
-----------------------------------------------------------------------


CREATE TABLE Category(
	CategoryID INT IDENTITY(1,1) NOT NULL,
	DepartmentID INT NOT NULL,
	Name NVARCHAR(50) NOT NULL,
	Description NVARCHAR(1000) NULL,
 CONSTRAINT PK_Category_1 PRIMARY KEY CLUSTERED(CategoryID ASC)
)

GO

ALTER TABLE Category ADD CONSTRAINT FK_Category_Department FOREIGN KEY(DepartmentID)
REFERENCES Department (DepartmentID)

GO

TRUNCATE TABLE Category
GO

SET IDENTITY_INSERT Category ON
GO

INSERT INTO Category (CategoryID, DepartmentID, Name, Description )		
VALUES (1, 1, 'Love & Romance', 'Here''s our collection of balloons with romantic messages.')

INSERT INTO Category (CategoryID, DepartmentID, Name, Description )		
VALUES (2, 1, 'Birthdays', 'Tell someone "Happy Birthday" with one of these wonderful balloons!')

INSERT INTO Category (CategoryID, DepartmentID, Name, Description )		
VALUES (3, 1, 'Weddings', 'Going to a wedding? Here''s a collection of balloons for that special event!')

INSERT INTO Category (CategoryID, DepartmentID, Name, Description )		
VALUES (4, 2, 'Message Balloons', 'Why write on paper, when you can deliver your message on a balloon?')

INSERT INTO Category (CategoryID, DepartmentID, Name, Description )		
VALUES (5, 2, 'Cartoons', 'Buy a balloon with your child''s favorite cartoon character!')

INSERT INTO Category (CategoryID, DepartmentID, Name, Description )		
VALUES (6, 2, 'Miscellaneous', 'Various baloons that your kid will most certainly love!')

GO

SET IDENTITY_INSERT Category OFF
GO

----------------------------------------
GO

CREATE TABLE ProductCategory(
	ProductID INT NOT NULL,
	CategoryID INT NOT NULL,
 CONSTRAINT PK_ProductCategory PRIMARY KEY CLUSTERED (ProductID ASC, CategoryID ASC)
) 

GO

ALTER TABLE ProductCategory  WITH CHECK ADD CONSTRAINT FK_ProductCategory_Category FOREIGN KEY(CategoryID)
REFERENCES Category (CategoryID)

GO

ALTER TABLE ProductCategory  WITH CHECK ADD  CONSTRAINT FK_ProductCategory_Product FOREIGN KEY(ProductID)
REFERENCES Product (ProductID)


GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(1, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(1, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(2, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(2, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(2, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(3, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(3, 3)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(3, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(4, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(4, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(4, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(4, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(5, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(6, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(6, 3)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(6, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(7, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(8, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(9, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(10, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(11, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(12, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(12, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(13, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(13, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(14, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(14, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(15, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(16, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(16, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(17, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(17, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(18, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(18, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(19, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(19, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(19, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(20, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(20, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(21, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(21, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(21, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(22, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(22, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(23, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(23, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(24, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(25, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(26, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(26, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(28, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(28, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(28, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(29, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(30, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(30, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(31, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(32, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(33, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(34, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(35, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(36, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(37, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(37, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(38, 4)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(38, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(39, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(40, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(41, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(42, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(43, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(44, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(45, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(46, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(47, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(48, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(49, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(50, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(51, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(52, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(53, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(53, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(54, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(54, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(55, 5)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(55, 6)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(56, 3)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(57, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(57, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(57, 3)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(58, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(58, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(58, 3)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(59, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(59, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(59, 3)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(60, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(60, 2)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(60, 3)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(61, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(61, 3)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(62, 1)
GO

INSERT INTO ProductCategory(ProductID, CategoryID)		
VALUES(62, 3)
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2009-10-20 : 12:20:52
since you've many to one relation from Product to category you need to specify what values you need to retrieve with distinct productid values (ie. first,last or random category). using distinct dont mean you get distinct values of productid, it just ensures unique combination of values
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site