Stored Procedure logic problem...

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

SQL Server Development (2000)

Stored Procedure logic problem...

Author

Topic

infrontweb
Starting Member

13 Posts

Posted - 2002-07-18 : 10:44:12

I am using the following SP to return data from 2 tables:
-------------
CREATE PROCEDURE Projects_sp
@vesselMakeID int
AS
SELECT p.projectID, p.name, p.description, p.projectActive, p.new, p.vesselMakeID, f.projectID, f.projectPhotoID, f.photo, f.photoActive
FROM projects p
INNER JOIN projectPhotos f ON
p.projectID = f.projectID
and p.vesselMakeID = @vesselMakeID
GO
--------------
Easy enough. So our relationship is 1 project to many photos where the project = vesselMakeID.

The SP the way I have it returns ALL projects and ALL photos related to that project (where the project = vesselMakeID).

Now what I am looking to do is returns ALL projects and 1 related photo (where the project = vesselMakeID). That's it. Any help would be great. Thanks.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-18 : 10:54:55

CREATE PROCEDURE Projects_sp
@vesselMakeID int
AS
SELECT p.projectID, p.name, p.description, p.projectActive, p.new, p.vesselMakeID, f.projectID, f.projectPhotoID, f.photo, f.photoActive,
(SELECT Top 1 f.projectPhotoID FROM projectPhotos f WHERE f.projectID = p.projectID) as projectPhotoID,
(SELECT Top 1 f.photo FROM projectPhotos f WHERE f.projectID = p.projectID) as photo,
(SELECT Top 1 f.photoActive FROM projectPhotos f WHERE f.projectID = p.projectID) as photoActive

FROM projects p
WHERE p.vesselMakeID = @vesselMakeID
GO

<Yoda>Use the Search page you must. Find the answer you will.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-07-18 : 10:57:53

instead of
INNER JOIN projectPhotos f ON
p.projectID = f.projectID
and p.vesselMakeID = @vesselMakeID

Use a where statement

INNER JOIN projectPhotos f ON
p.projectID = f.projectID
WHERE p.vesselMakeID = @vesselMakeID

hopefully this is what you want... otherwise, Sorry I didn't quite catch your meaning. If I'm wrong with what you want there, leave a post with you tables ddl (create statements) so I can see how the table is set up. Maybe an example of the results you want.

-----------------------
Take my advice, I dare ya

infrontweb
Starting Member

13 Posts

Posted - 2002-07-18 : 12:05:18

The idea from MichaelP has several syntax errors so I am unable to check it out.

The idea from M.E. produces the same result as the original SP.

What I am looking for is to retrieve just 1 photo for each project the asp page lists out in the repeat region. I have a page that displays a list of marine projects based on vesselMakeID. I have a separate table that has all the photos for all the projects. There is no limit to the amount of photos that can be related to any given project. Since this list page is a stepping stone to the project detail page I would only like to display 1 photo for each project to give a tease to the visitor rather than all photos.

The existing SP lists out ALL the projects AND ALL the photos related to its specific project.

I.E. - projectID = 12, project 12 has 3 photos in a separate table. Photo 1 - photoID = 34, projectID = 12
Photo 2 - photoID = 35, projectID = 12
Photo 3 - photoID = 36, projectID = 12

The list would display the project title (projectID (12)) and 1 of the many photos related to it(photoID (34)).

Click on the project title(link) and go to the project detail page with ALL the photos.

Here are my tables:

CREATE TABLE [dbo].[projects] (
[projectID] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[description] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[equipment] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[specialPoints] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[projectActive] [int] NULL ,
[new] [int] NULL ,
[vesselTypeID] [int] NULL ,
[vesselMakeID] [int] NULL ,
[projectGroupID] [int] NULL ,
[manufacturerID] [int] NULL ,
[timestamp] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[projectPhotos] (
[projectPhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[projectID] [int] NOT NULL ,
[name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[photo] [image] NULL ,
[photo_yesno] [int] NULL ,
[photo_type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[thumb] [image] NULL ,
[thumb_yesno] [int] NULL ,
[thumb_type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[height] [int] NULL ,
[width] [int] NULL ,
[layout] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[photoActive] [int] NULL ,
[timestamp] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Thanks for all the help.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-18 : 13:13:12

Several Syntax errors for mine? Doh! I see the problem. Try this:

CREATE PROCEDURE Projects_sp
@vesselMakeID int
AS
SELECT p.projectID, p.name, p.description, p.projectActive, p.new, p.vesselMakeID,
(SELECT Top 1 f.projectPhotoID FROM projectPhotos f WHERE f.projectID = p.projectID) as projectPhotoID,
(SELECT Top 1 f.photo FROM projectPhotos f WHERE f.projectID = p.projectID) as photo,
(SELECT Top 1 f.photoActive FROM projectPhotos f WHERE f.projectID = p.projectID) as photoActive
FROM projects p
WHERE p.vesselMakeID = @vesselMakeID

The basic concept for teh code I gave you will give you what you are looking for.

What version of SQL Server are you running??

Michael

<Yoda>Use the Search page you must. Find the answer you will.

infrontweb
Starting Member

13 Posts

Posted - 2002-07-18 : 16:19:38

Michael, Thanks for all the help. I am running sql2000.
I think I'm starting to grasp what you're trying to do.

Still getting a syntax error:
Microsoft SQL-DMO (ODBC SQLstate: 42000)

Error 279: the text, ntext, and image data types are invalid in this subquery or aggregate expression.

I,ve tried a few things but was unable to repair.
Any ideas?
Eric

MuffinMan
Posting Yak Master

107 Posts

Posted - 2002-07-18 : 16:59:24

How about:

SELECT p.projectID, p.name, p.description,
p.projectActive, p.new, p.vesselMakeID,
f.projectID, f.projectPhotoID, f.photo, f.photoActive
FROM projects p
INNER JOIN
(Select projectID, Min(projectPhotoID) as projectPhotoID
from projectPhotos
GROUP BY projectID) m
ON p.projectID = m.projectID
inner join projectPhotos f
ON p.projectID = f.projectID and m.projectPhotoID = f.projectPhotoID
where p.vesselMakeID = @vesselMakeID

Edited by - muffinman on 07/18/2002 17:14:53

infrontweb
Starting Member

13 Posts

Posted - 2002-07-18 : 17:21:30

HOT DOG.
It working perfectly.
Thanks to all who helped me on this problem.
Many, many thanks.
Keep up the good work guys.

Eric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources