February 27, 2009 at 10:49 am
Hello,
I posted a question earlier in the week but I wasn't clear and didn't provide enough sample code so I'd like to try again. Thanks for your patience.
I have three tables. Images contains information about images located on the webserver, Items contains product information and Relationships contains data to link the Items to the Images. I need to store the data in this way because some Images contain multiple products and some products have more than one associated image. So basically I have a Many:Many relationship.
I would like to perform an inner join on all the tables like such:
select * from Images, Items, Relationships
where Images.ImageID = Relationships.ImageID and
Items.ItemID = Relationships.ItemID
order by Items.ItemName
However, because of the Many:Many relationship I'm getting image3 listed twice. I need to eliminate all duplicate Images.ImageID rows because the output shouldn't show two of the same image. I would like to somehow use DISTINCT to eliminate the duplicates but of course I won't get all the other columns in my output, only the ImageID column so I can't sort it by any other column if I'm using distinct.
Does anyone have any suggestions for me? The code below will allow you to create a sample of my data.
Thanks for any help you can provide.
Dave Williams
/****** Object: Table [dbo].[Images] Script Date: 02/27/2009 12:25:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Images](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[FilePath] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[Items] Script Date: 02/27/2009 12:25:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Items](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[ItemName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** Object: Table [dbo].[Relationships] Script Date: 02/27/2009 12:26:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Relationships](
[RelationshipID] [int] IDENTITY(1,1) NOT NULL,
[ItemID] [int] NULL,
[ImageID] [int] NULL
) ON [PRIMARY]
Insert INTO Images (FilePath, FileName)
SELECT'Images/','Image1.jpg' UNION ALL
SELECT'Images/','Image2.jpg' UNION ALL
SELECT'Images/','Image3.jpg' UNION ALL
SELECT'Images/','Image4.jpg'
Insert INTO Items (ItemName)
SELECT'Item1' UNION ALL
SELECT'Item2' UNION ALL
SELECT'Item3' UNION ALL
SELECT'Item4'
Insert INTO Relationships (ItemID, ImageID)
SELECT'1','1' UNION ALL
SELECT'1','2' UNION ALL
SELECT'2','3' UNION ALL
SELECT'3','3' UNION ALL
SELECT'4','4'
February 27, 2009 at 11:02 am
excellent post by providing the table and data...thank you!
The thing is, DISTINCT is used against all selected columns...so if you select * from 3 tables, you probably are not going to eliminate much. you could drop specific columns, or change some of the tables to sub selects to limit their data.
this SQL removed the RelationshipID, which was unique, so it showed up in all rows.
select * from Images, Items,
(SELECT DISTINCT Relationships.ITEMID,Relationships.IMAGEID FROM Relationships) Relationships
where Images.ImageID = Relationships.ImageID and
Items.ItemID = Relationships.ItemID
order by Items.ItemName
you'll see that image3 is repeated, but that is because it is invloved in two different realtinoships...is that what you want, or are you after only one row per image, ignoring duplicates ITEMID as well?
select * from Images, Items,
(SELECT MIN(Relationships.ITEMID) AS ITEMID,Relationships.IMAGEID FROM Relationships GROUP BY Relationships.IMAGEID) Relationships
where Images.ImageID = Relationships.ImageID and
Items.ItemID = Relationships.ItemID
order by Items.ItemName
Lowell
February 27, 2009 at 11:11 am
It doesn't matter which row is eliminated in this case because I'll really only be using the FilePath and FileName from the returned results, which will be the same on all duplicate rows from Images. The only reason that I need all the columns is because the user may want to sort by any particular column, as well, I will need to further eliminate rows based on user searches, but I can do all of that with a where clause.
I've been playing with the idea of creating a temporary table with a new ID column to retain the sort order, but I'm getting lost in my code a little so I thought that posting just the data and requirements may allow more creative solutions.
Thanks for your response.
Dave
February 27, 2009 at 12:18 pm
That's great, it works perfectly.
Thanks again!
Dave
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply