Linking two tables with a relationship

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

SQL Server 2000 Forums

Transact-SQL (2000)

Linking two tables with a relationship

Author

Topic

ianbowyer
Starting Member

2 Posts

Posted - 2005-02-27 : 10:40:01

Hi I am doing a personal online gallery and have an SQL problem which should be so simple but is proving challenging. I may be looking at the problem from the wrong angle.

The proble is as follows... I have two tables "Album" and "picture" (along with other ;o) )
The create statements are as follows:

CREATE TABLE [dbo].[Album] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Parent] [int] NULL ,
[sort] [int] NULL ,
[comments] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[timestamp] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Picture] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,
[Description] [varchar] (8000) COLLATE Latin1_General_CI_AS NULL ,
[FileName] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Sort] [int] NOT NULL ,
[AlbumID] [int] NOT NULL ,
[CreatedBY] [int] NULL ,
[TimeStamp] [datetime] NOT NULL
) ON [PRIMARY]
GO

What I have done is to have a procedure which only shows albums which have pictures in using the following SQL Statement:

SELECT distinct(A.ID), A.Name, A.Parent, A.Sort, A.Comments
FROM Album A
JOIN Picture P ON P.AlbumID = A.ID
order by A.Sort

Which will for example show
<code>
ID Name Parent Sort Comments
1 Album1 NULL 10 Comment 1
2 Album2 NULL 20 Comment 2
3 Album3 NULL 30 Comment 3
</code>
What I want to do is to have a filename column which will be the top Picture from the related table. The top Picture for each album is the defined from picture->sort field (the lowest number being the top).

I tried a group to no avail:
SELECT A.ID, A.Name, A.Parent, A.Sort, A.Comments,P.filename
FROM Album A JOIN Picture P ON P.AlbumID = A.ID
GROUP BY A.ID,P.filename, A.Name, A.Parent, A.Sort, A.Comments

Which returned something like
ID Name Parent Sort Comments FileName
1 Album1 NULL 10 Comment 1 FileName1.JPG
1 Album1 NULL 10 Comment 1 FileName2.JPG
1 Album1 NULL 10 Comment 1 FileName3.JPG
2 Album2 NULL 20 Comment 2 FileName4.JPG
3 Album3 NULL 30 Comment 3 FileName5.JPG

Am I missing something obvious... I know I can probably curse through origional SQL and do a select on picture to pick out the top 1 where album = cursor->albumID but really dont want to do that unless I have to.

Any help is much appreciated

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-02-27 : 11:41:39

This code snippet should get you where you want to go. Let us know if you need anymore help.

You need to do something like this:


USE Northwind
GO
DROP TABLE Item
GO
CREATE TABLE Item(
	PeriodID INT,
	SupplierID INT,
	ProductName VARCHAR(255),
	PricePerKg MONEY)
GO
INSERT Item(PeriodID, SupplierID, ProductName, PricePerKg)
	SELECT 1, 1, 'Alfafa', 9.2857 UNION ALL
	SELECT 1, 2, 'Alfafa', 8.9394 UNION ALL
	SELECT 1, 3, 'Alfafa', 6.6667 UNION ALL
	SELECT 1, 1, 'Apricots 40-45mm', 2.1000 UNION ALL
	SELECT 1, 2, 'Apricots 40-45mm', 3.0000 UNION ALL
	SELECT 1, 3, 'Apricots 40-45mm', 2.1500

SELECT 
	i1.SupplierID, 
	i2.ProductName, 
	i2.PricePerKg
FROM 
	Item i1
	INNER JOIN (
		SELECT 
			ProductName, 
			MIN(PricePerKg) AS PricePerKg
		FROM 
			Item
		WHERE 
			PeriodID = 1
			AND PricePerKg > 0
		GROUP BY 
			ProductName) i2 ON i1.ProductName = i2.ProductName
		AND i1.PricePerKg = i2.PricePerKg

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

ianbowyer
Starting Member

2 Posts

Posted - 2005-02-27 : 15:09:56

Hi derrickleggett thanks for your help but still having problems :o(
I like the idea of showing the min sort of the table grouped by AlbumID

I have adopted you query to fit my example and looks like below

SELECT A.ID,
A.Name,
A.Parent,
A.Sort,
A.Comments,
P.FileName1
FROM
Album A
INNER JOIN (
SELECT AlbumID, Min(sort) AS SortMin, filename as filename1
FROM Picture
GROUP BY AlbumID

) P ON A.ID = P.AlbumID

The problem is selected in bold. It returns:
Column 'Picture.FileName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Can you shed any more light to the subject anyone

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-02-27 : 15:46:50

You need to INNER JOIN to Picture again. Remember, you have to have all columns in the group by not aggregated, so in the derived table, just have AlbumID and SortMin. Inner join the derived table to Picture again, joining on AlbumID. That should give you the result you want.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources