Finding related items - SQL Server Forums

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

Database Design and Application Architecture

Finding related items

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2007-06-07 : 09:42:22

I was thinking last night of restructuring the way "Related Items" are pulled from the database for one of our sites. Basically at the moment it is upto the client to flag two products as related. I'm thinking of using a new method of tagging the products and then finding related products based on the tags assigned -the general code for this is below but what I want to know is, is using "IN" the most efficient way of doing this?

TIA

Tim


DECLARE @ItemId int
SET @ItemId = 1

SELECT
	COUNT(i.ItemId) AS MatchingTags,
	i.ItemId,
	i.ItemName
FROM
	tst_Items i LEFT JOIN txt_x_Items_Tags x
		ON i.ItemId = x.ItemId
WHERE
	x.TagId IN (
					SELECT
						t.TagId
					FROM
						tst_Tags t LEFT JOIN txt_x_Items_Tags x
							ON t.TagId = x.TagId
					WHERE
						x.ItemId = @ItemId
				)
GROUP BY
	i.ItemId,
	i.ItemName
HAVING
	i.ItemId <> @ItemId
ORDER BY
	MatchingTags DESC

The testing data:


CREATE TABLE [dbo].[tst_Items](
	[ItemId] [int] NOT NULL,
	[ItemName] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tst_Tags](
	[TagId] [int] NOT NULL,
	[TagName] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[txt_x_Items_Tags](
	[ItemId] [int] NOT NULL,
	[TagId] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO tst_Items VALUES (1, 'Test Item 1')
INSERT INTO tst_Items VALUES (2, 'Test Item 2')
INSERT INTO tst_Items VALUES (3, 'Test Item 3')
INSERT INTO tst_Items VALUES (4, 'Test Item 4')

INSERT INTO tst_Tags VALUES (1, 'Tag 1')
INSERT INTO tst_Tags VALUES (2, 'Tag 2')
INSERT INTO tst_Tags VALUES (3, 'Tag 3')
INSERT INTO tst_Tags VALUES (4, 'Tag 4')

INSERT INTO txt_x_Items_Tags VALUES (1, 1)
INSERT INTO txt_x_Items_Tags VALUES (1, 2)
INSERT INTO txt_x_Items_Tags VALUES (1, 3)
INSERT INTO txt_x_Items_Tags VALUES (2, 1)
INSERT INTO txt_x_Items_Tags VALUES (2, 3)
INSERT INTO txt_x_Items_Tags VALUES (3, 1)
INSERT INTO txt_x_Items_Tags VALUES (3, 2)
INSERT INTO txt_x_Items_Tags VALUES (1, 4)
INSERT INTO txt_x_Items_Tags VALUES (4, 4)
GO

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

timgaunt
Posting Yak Master

115 Posts

Posted - 2007-06-08 : 07:56:12

I think this is a better method.


DECLARE @ItemId int
SET @ItemId = 1

SELECT
	pX.ItemId AS pId, 
	i.ItemName, 
	COUNT(i.ItemId) AS MatchingTags
FROM
	dbo.txt_x_Items_Tags AS x 
		RIGHT OUTER JOIN dbo.txt_x_Items_Tags AS pX 
			ON x.TagId = pX.TagId 
		LEFT OUTER JOIN dbo.tst_Items AS i 
			ON x.ItemId = i.ItemId
WHERE
	x.ItemId <> @ItemId
GROUP BY 
	pX.ItemId, 
	i.ItemName
HAVING
	(pX.ItemId = @ItemId)
ORDER BY 
	MatchingTags DESC

----------------------------
I've finally started blogging (all be it badly!)

Check it out:
http://blogs.thesitedoctor.co.uk/tim/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources