Help a newbie and live forever... maybe

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

Help a newbie and live forever... maybe

Author

Topic

hubare
Starting Member

11 Posts

Posted - 2006-02-22 : 11:12:35

Hi, sorry if this has been asked before but I'm pretty strapped for time...

I have two tables: [photos] and [photoFolders]

[photoFolders] contains information about photo albums on the site im creating. The information in [photos] lists information about all photos along with which [photoFolder] they belong. When the user logs in, I want to present a list of all 'folders' in their name along with the TOP image with the corresponding folderId...

[photos]
photoId
photoName
folderId
photoDescription
cUserId

[photoFolders]
folderId
folderName
eventDate
cUserId

Any help would be GREATLY appreciated

We're all going to hell... I guess I'll see you there!

Kristen
Test

22859 Posts

Posted - 2006-02-22 : 12:17:26

Hi hubare, Weclome to SQL Team!

Something like this you mean?


SELECT folderName,
       photoId,
       photoName,
       folderId,
       photoDescription,
       cUserId
(
    SELECT folderName, MIN(photoId) AS MIN_photoId
    FROM photoFolders AS PF
         JOIN photos AS P
             ON P.folderId = PF.folderId
    WHERE P.cUserId = 'PunterName'
    GROUP BY folderName
) AS X
    JOIN photos AS P
        ON P.photoId = X.MIN_photoId

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-22 : 12:24:01

quote:
"Help a newbie and live forever... maybe"

Long live Kristen !

----------------------------------
'KH'
It is inevitable

Kristen
Test

22859 Posts

Posted - 2006-02-22 : 13:03:39

I don't like the "maybe" bit very much ...

hubare
Starting Member

11 Posts

Posted - 2006-02-22 : 15:03:07

quote:
Originally posted by Kristen

I don't like the "maybe" bit very much ...

Can't test it until tomorrow, but I thought I'd say thank you

Fank yooooo

We're all going to hell... I guess I'll see you there!

hubare
Starting Member

11 Posts

Posted - 2006-02-23 : 04:29:57

quote:
Originally posted by Kristen

I don't like the "maybe" bit very much ...

Erm.... sorry to be a thorough nob-end

... but could you explain what this string is doing... I can't get it to work...

Love Live Kristen...!!!

We're all going to hell... I guess I'll see you there!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 04:40:50

Post some sample data and expected result.

----------------------------------
'KH'
It is inevitable

hubare
Starting Member

11 Posts

Posted - 2006-02-23 : 04:50:22

quote:
Originally posted by khtan

Post some sample data and expected result.

----------------------------------
'KH'
It is inevitable

Right... well... I'll start again...

The website is for a wedding photographers. In the user's homepage I wanna list all folders (albums) and with each folder I want one photo from that album...

So the [photoFolders] contains the information about the folder as a whole such as the name, description etc.

The [photos] are linked to the folders through the folderId field which simply lets the site know which folder it belongs to...

I kind of understand what Kristen was trying to do but it seams not to be working.

I basically want a string that will populate a recordset that contains the following (pseudocode)...

SELECT photoFolders.* and MIN(photos.photoName) WHERE photos.folderId = photoFolders.folderId AND photoFolders.cUserId = >>>user input<<<

We're all going to hell... I guess I'll see you there!

hubare
Starting Member

11 Posts

Posted - 2006-02-23 : 04:52:28

quote:
Originally posted by khtan

Post some sample data and expected result.

----------------------------------
'KH'
It is inevitable

PS... This is the first project I've been let loose on and I want to make a nice impact... I totally appreciate this, although I feel bad because normally I'd buy people who help me a pint...

We're all going to hell... I guess I'll see you there!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 05:08:39

quote:
I can't get it to work...

Is it NOT WORKING OR "Incorrect syntax near the keyword 'SELECT'."

Actually, Kristen's code is fine except for the missing FROM

SELECT folderName,
       photoId,
       photoName,
       folderId,
       photoDescription,
       cUserId
FROM
(
    SELECT folderName, MIN(photoId) AS MIN_photoId
    FROM photoFolders AS PF
         JOIN photos AS P
             ON P.folderId = PF.folderId
    WHERE P.cUserId = 'PunterName'
    GROUP BY folderName
) AS X
    JOIN photos AS P
        ON P.photoId = X.MIN_photoId

----------------------------------
'KH'
It is inevitable

hubare
Starting Member

11 Posts

Posted - 2006-02-23 : 05:11:03

quote:

Originally posted by khtan

quote:
I can't get it to work...

Is it NOT WORKING OR "Incorrect syntax near the keyword 'SELECT'."

Actually, Kristen's code is fine except for the missing FROM

SELECT folderName,
       photoId,
       photoName,
       folderId,
       photoDescription,
       cUserId
FROM
(
    SELECT folderName, MIN(photoId) AS MIN_photoId
    FROM photoFolders AS PF
         JOIN photos AS P
             ON P.folderId = PF.folderId
    WHERE P.cUserId = 'PunterName'
    GROUP BY folderName
) AS X
    JOIN photos AS P
        ON P.photoId = X.MIN_photoId

----------------------------------
'KH'
It is inevitable

Thanks!!! It works... thanks a lot

We're all going to hell... I guess I'll see you there!

Kristen
Test

22859 Posts

Posted - 2006-02-23 : 05:13:34

Doh! Sorry guys ... there goes eternal life

hubare
Starting Member

11 Posts

Posted - 2006-02-23 : 05:46:35

quote:
Originally posted by Kristen

Doh! Sorry guys ... there goes eternal life

ERM.... sorry guys.... my excitment is shortlived...

it only returns the first folder.... I need it to return all folders regardless of the existance of a photo in the folder...

sorry to be a pain...

We're all going to hell... I guess I'll see you there!

hubare
Starting Member

11 Posts

Posted - 2006-02-23 : 05:51:16

lol.... in fact its better this way... forget my last post... im just mad and talking to myself in a forum... eventhough its not exactly what i wanted its improved the design... hoorah

We're all going to hell... I guess I'll see you there!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 06:36:39

"it only returns the first folder.... I need it to return all folders regardless of the existance of a photo in the folder..."
Just in case you ever need this. Change the JOIN to LEFT JOIN for the inner query.

----------------------------------
'KH'
It is inevitable

Kristen
Test

22859 Posts

Posted - 2006-02-23 : 09:44:04

"... even though its not exactly what i wanted its improved the design ..."

I can help you with a definition for Acceptance Tests then:

quote:

Continuous rewrites of the specification until they meet the product provided

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources