By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

HELP, How do I do this??

P: n/a
This should be fairly basic but I can't think of how to do this and I'm
running out of time!

I am developing a picture gallery and I can't figure out how to select
"one" picture from each gallery. My DB is configured like this...

tbl_pictures
this contains all pictures in the picture gallery.

tbl_pictures_galleries
this contains all the picture gallery names that can be created by the
client. It's primary key acts as a foreign key in the tbl_pictures
table.

My problem: I don't know how to select the latest picture from each
gallery (identified by FK) via a loop. This should be so simple but
I'm just not seeing it right now.

Help greatly appreciated!!

Thanks!!!

Jul 22 '05 #1
Share this Question
Share on Google+
17 Replies


P: n/a
http://www.aspfaq.com/5009

Ray at home

"the other john" <ki*****@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
This should be fairly basic but I can't think of how to do this and I'm
running out of time!

I am developing a picture gallery and I can't figure out how to select
"one" picture from each gallery. My DB is configured like this...

tbl_pictures
this contains all pictures in the picture gallery.

tbl_pictures_galleries
this contains all the picture gallery names that can be created by the
client. It's primary key acts as a foreign key in the tbl_pictures
table.

My problem: I don't know how to select the latest picture from each
gallery (identified by FK) via a loop. This should be so simple but
I'm just not seeing it right now.

Help greatly appreciated!!

Thanks!!!

Jul 22 '05 #2

P: n/a
Oops - I assumed he was using Access and provided a solution for Access.

To "the other john":
if you are not using Access, the "Last" function may not exist in the brand
of sql you are using. The second example is the one that will work in most
databases.

Bob Barrows
Ray Costanzo [MVP] wrote:
http://www.aspfaq.com/5009

Ray at home

"the other john" <ki*****@yahoo.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
This should be fairly basic but I can't think of how to do this and
I'm running out of time!

I am developing a picture gallery and I can't figure out how to
select "one" picture from each gallery. My DB is configured like
this... tbl_pictures
this contains all pictures in the picture gallery.

tbl_pictures_galleries
this contains all the picture gallery names that can be created by
the client. It's primary key acts as a foreign key in the
tbl_pictures table.

My problem: I don't know how to select the latest picture from each
gallery (identified by FK) via a loop. This should be so simple but
I'm just not seeing it right now.

Help greatly appreciated!!

Thanks!!!


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #3

P: n/a
You assumed correctly on the Access. I'll try this out and get back
today.

Jul 22 '05 #4

P: n/a
ooooh boy, my head is spinning on the aliasing.....wow. I start losing
it when you start useing "q". p for the pictures table and g for the
galleries table....what is the q?

Thanks!

Jul 22 '05 #5

P: n/a
ok, I'm getting this error.....

Microsoft JET Database Engine error '80004005'

The specified field 'p.PK_Picture_Gallery_ID' could refer to more than
one table listed in the FROM clause of your SQL statement.
this is the statement I wrote based on what you suggested. What did I
get wrong?

gallerySQL = "SELECT g.fld_Picture_Gallery_fileName,
p.PK_Picture_Gallery_ID FROM (tbl_pictures_galleries AS g " & _
"INNER JOIN tbl_Pictures AS p ON g.PK_Picture_Gallery_ID =
p.FK_Picture_galleryID) " & _
"INNER JOIN (SELECT p.FK_Picture_galleryID,
Max(p.fld_picture_DateTime) AS LastDate FROM tbl_Pictures AS p GROUP BY
p.fld_picture_galleryID) " & _
"AS q ON p.fld_picture_galleryID = q.GalleryID and
p.fld_picture_DateTime=q.LastDate;"

Thanks!

Jul 22 '05 #6

P: n/a
the other john wrote:
ooooh boy, my head is spinning on the aliasing.....wow. I start
losing it when you start useing "q". p for the pictures table and g
for the galleries table....what is the q?

To me, the aliases make it much more readable. The q is the alias for the
subquery (the select statement enclosed in parenthes). With a subquery, aka
derived or virtual table, there is no physical table so you HAVE to use an
alias.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #7

P: n/a
the other john wrote:
ok, I'm getting this error.....

Microsoft JET Database Engine error '80004005'

The specified field 'p.PK_Picture_Gallery_ID' could refer to more than
one table listed in the FROM clause of your SQL statement.
this is the statement I wrote based on what you suggested. What did I
get wrong?

gallerySQL = "SELECT g.fld_Picture_Gallery_fileName,
p.PK_Picture_Gallery_ID FROM (tbl_pictures_galleries AS g " & _
"INNER JOIN tbl_Pictures AS p ON g.PK_Picture_Gallery_ID =
p.FK_Picture_galleryID) " & _
"INNER JOIN (SELECT p.FK_Picture_galleryID,
Max(p.fld_picture_DateTime) AS LastDate FROM tbl_Pictures AS p GROUP
BY p.fld_picture_galleryID) " & _
"AS q ON p.fld_picture_galleryID = q.GalleryID and
p.fld_picture_DateTime=q.LastDate;"


Why aren't you testing this in Access using the Query Builder? Always get
your queries running in Access before attempting to make them run from ASP.
That way when you run into problems, you know where start looking for the
problem.

I'm not sure where the problem is. The example I posted worked fine when I
tried it in Access. I suggest modifying the subquery - since the subquery
only has a single table, it does not need an alias: the field names do not
need to be qualified.

"INNER JOIN (SELECT FK_Picture_galleryID,
Max(fld_picture_DateTime) AS LastDate FROM tbl_Pictures GROUP BY
fld_picture_galleryID) " & _
"AS q

If you feel you absolutely MUST qualify all your field names, then use a
different alias than p in the subquery
I would not have expected there to be any confusion between the table
aliases given that one alias was inside the subquery, however, this would
not be the first time I've been surprised by Jet.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #8

P: n/a
I'm still kind of a newbie. Not quite but not far removed. I'm workin
at it and I'm sure these will get easier. Just when I think "oh, ok,
I'm getting kind of good at this" something comes up and I go "OOooooh,
well, not that good yet" ;-P Hope I didn't imply that what you're
telling me isn't good, on the contrary....hoping to get there myself is
all ;-)

Jul 22 '05 #9

P: n/a
Oh, I should have added that I don't really know how to use the Query
build in Access that well. I've played around with it but not knowing
how to make it flexible enough to just "do" something I want it to I
end up just writing the statements from stratch and testing until I
either tear out my hair or get it right. I used Views with sql server
via access and that seems more logical or at least easier to use than
Access. I've still got a lot to learn...just need to find places to do
that.

Jul 22 '05 #10

P: n/a
the other john wrote:
Oh, I should have added that I don't really know how to use the Query
build in Access that well.


Don't let that stop you. Open the Query Builder and switch to SQL View - no
need to use the GUI if you don't want to. The iea is to create and test your
queries in the environment where they will actually run, using the tools in
access to help you optimize their performance (the Analyzer is a very good
tool to use. depending on the version of access you are using, it can be
found in Tools/Analyze). The problem with doing everything from asp is that
you never have a chance to perform optimizations. Also, if you get into the
habit of saving and parameterizing your queries, you will find them a lot
simpler (and more secure) to execute from asp as opposed to the klunky and
insecure dynamic sql you are creating. See:

http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

http://groups.google.com/groups?hl=e...tngp13.phx.gbl
http://groups-beta.google.com/group/...d322b882a604bd
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #11

P: n/a
This is a query I ran in query builder. It doesn't do everything I want
it to do yet but I have some questions....
SELECT tbl_Pictures.FK_picture_galleryID,
tbl_Pictures_Galleries.PK_Picture_Gallery_ID,
tbl_Pictures.fld_picture_DateTime,
tbl_Pictures_Galleries.fld_Picture_Gallery_Name
FROM tbl_Pictures_Galleries INNER JOIN tbl_Pictures ON
tbl_Pictures_Galleries.PK_Picture_Gallery_ID =
tbl_Pictures.FK_picture_galleryID
WHERE (((tbl_Pictures_Galleries.PK_Picture_Gallery_ID)=5 ))
ORDER BY tbl_Pictures.fld_picture_DateTime DESC;

what's with all the parentheses??

Also, I tried to filter by using MAX() on the DateTime field but got an
error that said...
"You tried to execute a query that does not include the specified
expression'FK_picture_galleryID' as part of an aggregate function". I
don't know what this means. I know that aggregate means to summarize
but I don't know why this won't work.

Also, I'll have to dynamically loop through the gallery ID's in the
WHERE clause. Do I just open a seperate recordset for these and embed
the statement I've been working on above within it?

Thanks again! My deadline is getting close and I'm starting to sweat
now.

Jul 22 '05 #12

P: n/a
the other john wrote:
This is a query I ran in query builder. It doesn't do everything I
want it to do yet but I have some questions....
SELECT tbl_Pictures.FK_picture_galleryID,
tbl_Pictures_Galleries.PK_Picture_Gallery_ID,
tbl_Pictures.fld_picture_DateTime,
tbl_Pictures_Galleries.fld_Picture_Gallery_Name
FROM tbl_Pictures_Galleries INNER JOIN tbl_Pictures ON
tbl_Pictures_Galleries.PK_Picture_Gallery_ID =
tbl_Pictures.FK_picture_galleryID
WHERE (((tbl_Pictures_Galleries.PK_Picture_Gallery_ID)=5 ))
ORDER BY tbl_Pictures.fld_picture_DateTime DESC;

what's with all the parentheses??
Yeah, I know. The Access Builder takes no chances. Everyting it considers to
be an expression is surrounded by parentheses to prevent the query engine
from misinterpreting anything. The Query Builder does not always know
exactly what you mean so it takes no chances. The first thing I used to do
when using the Query Builder after switching to SQL View was: remove all the
damn parentheses ... except for the ones surrounding the joins in the FROM
clause - those seem to be required by Jet. Anyways, the above generated SQL
would be changed by me to (you don't seem to like using the shorter aliases,
so I'll leave the generated alieases used in the field qualifications
alone):

SELECT tbl_Pictures.FK_picture_galleryID,
tbl_Pictures_Galleries.PK_Picture_Gallery_ID,
tbl_Pictures.fld_picture_DateTime,
tbl_Pictures_Galleries.fld_Picture_Gallery_Name
FROM tbl_Pictures_Galleries INNER JOIN tbl_Pictures ON
tbl_Pictures_Galleries.PK_Picture_Gallery_ID =
tbl_Pictures.FK_picture_galleryID
WHERE tbl_Pictures_Galleries.PK_Picture_Gallery_ID=5
ORDER BY tbl_Pictures.fld_picture_DateTime DESC;


Also, I tried to filter by using MAX() on the DateTime field but got
an error that said...
"You tried to execute a query that does not include the specified
expression'FK_picture_galleryID' as part of an aggregate function". I
don't know what this means. I know that aggregate means to summarize
but I don't know why this won't work. Aggregate = result of summary (aggregate) function such as SUM,
MAX,MIN,FIRST, etc.

You cannot aggregate without using GROUP BY, unless all the fields in the
SELECT clause are aggregates. This also applies to the WHERE clause. In
fact, an aggregation cannot be used in the WHERE clause. If you need to
filter on an aggregate, it needs to be done in the HAVING clause. See the
link I cite below.
And if you do use GROUP BY, the only non-aggregates allowed in the select
are the columns listed in the GROUP BY clause.

Here's an old post I made that hopefully explains this:
*****************************************
Say you have a table with two columns containing the following 4 rows of
data:

Col1 Col2
1 28
1 33
2 5
2 8

Now you decide to create a grouping query:
select Col1 From table Group By Col1
You would get these results:
1
2

Now you decide to add Col2 to the select list:
select Col1,Col2 From table Group By Col1
Here are the results:
1 ?
2 ?

What do you replace the ?'s with? Do you see the problem?

Bottom line: You have to tell the query engine how to aggregate Col2 (min,
max,sum, avg,count, etc.) so that you wind up with a single row for each
value in Col1.

Even if Col2 contains the value 5 in every row, you still have to tell the
engine how to aggregate it. The query parser does not know what's in your
table: all it can see is that there is a column in the select list that
needs aggregation.
************************************************** *
You should also read this:
http://groups-beta.google.com/group/...194ab2109662c8
Also, I'll have to dynamically loop through the gallery ID's in the
WHERE clause.
I don't understand.
Do I just open a seperate recordset for these and embed
the statement I've been working on above within it?
I don't know. I don't understand what you're trying to do.
Thanks again! My deadline is getting close and I'm starting to sweat
now.

Sorry, but if you're looking for me to write it for you, I've got my own job
to do ... ;-)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #13

P: n/a
Thanks Bob, this is helping a lot. I'm just freakin' because I only
have a few more days to figure this out 8-o

Basically all I'm trying to do is select and display the latest picture
from each gallery. What is throwing me is that all the pictures are in
one table and only linked to a specific gallery via a FK in a gallery
table. So if there were 6 galleries I would need to display the latest
picture from each gallery in a row or something. What I was saying
earlier is all I can think of doing at my level is....

Open a recordset of gallery names and ID's
.......
Setup a For Each Loop
.......
Open another recordset that selects the latest picture from one gallery
.......
Display that picture and it's gallery name
.......
Go to the next gallery with it's latest picture
.......
Loop until all galleries have been displayed with their latest picture
.......
Close the Second recordset
.......
Close the First recordset

All I'm asking at this point is this an alright way to do it or will it
not work? I'm trying to avoid blind alleys at this point.

Thanks again!!

Jul 22 '05 #14

P: n/a
the other john wrote:
Thanks Bob, this is helping a lot. I'm just freakin' because I only
have a few more days to figure this out 8-o

Basically all I'm trying to do is select and display the latest
picture from each gallery. What is throwing me is that all the
pictures are in one table and only linked to a specific gallery via a
FK in a gallery table. So if there were 6 galleries I would need to
display the latest picture from each gallery in a row or something.
What I was saying earlier is all I can think of doing at my level
is....

Open a recordset of gallery names and ID's
......
Setup a For Each Loop
......
Open another recordset that selects the latest picture from one
gallery ......
Display that picture and it's gallery name
......
Go to the next gallery with it's latest picture
......
Loop until all galleries have been displayed with their latest picture
......
Close the Second recordset
......
Close the First recordset

All I'm asking at this point is this an alright way to do it or will
it not work? I'm trying to avoid blind alleys at this point.

It will work. It could be more efficient, but it will work. I don't have
time right now to explain how to improve the efficiency (hopefully someone
else will jump in) beyond saying that I don't think the second recordset is
necessary. I will check back in tomorrow and if nobody has chimed in, I will
attempt to explain further.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Jul 22 '05 #15

P: n/a
ok, this query worked when tested in Access...

SELECT g.fld_Picture_Gallery_Name, p.PK_picture_ID
FROM (tbl_pictures_galleries AS g INNER JOIN tbl_pictures AS p ON
g.PK_Picture_Gallery_ID = p.FK_picture_galleryID) INNER JOIN [SELECT
p.FK_picture_galleryID, Max(p.fld_picture_DateTime) AS LastDate
FROM tbl_pictures AS p
GROUP BY p.FK_picture_galleryID]. AS q ON p.fld_picture_DateTime =
q.LastDate;

The trouble is I don't understand it that well. When I tested it in
Access it asks for q.galleryID. If I enter a valid gallery number it
returns the result I'm looking for. My problem is I don't know where
in the query to pass this parameter in ASP. I have to learn this
sooner of later so I need 2 things.

1) the quick fix: where do I pass the parameter to this query to
provide it with the gallery ID

2) the logic to understand it: I need someone to break this down a
little so I can understand it.

I'm currently using Paul Wilton's "Beginning SQL" which is very helpful
and easy to understand however it doesn't quite cover a query of the
complexity. Any suggestions for getting better at this?

Thanks!!!!

Jul 22 '05 #16

P: n/a
the other john wrote:
ok, this query worked when tested in Access...

SELECT g.fld_Picture_Gallery_Name, p.PK_picture_ID
FROM (tbl_pictures_galleries AS g INNER JOIN tbl_pictures AS p ON
g.PK_Picture_Gallery_ID = p.FK_picture_galleryID) INNER JOIN [SELECT
p.FK_picture_galleryID, Max(p.fld_picture_DateTime) AS LastDate
FROM tbl_pictures AS p
GROUP BY p.FK_picture_galleryID]. AS q ON p.fld_picture_DateTime =
q.LastDate;

The trouble is I don't understand it that well. When I tested it in
Access it asks for q.galleryID.
It does??? Are you sure you're showing us the correct sql statement? The
only reason it would ask for "q.galleryID" is if that word appeared
somewhere in your query. I don't see it anywhere. In fact, the query does
not look correct. It's missing something from the final ON clause - there is
nothing to link the gallery id in the outer query to the gallery id in the
subquery. It should look like:

SELECT g.fld_Picture_Gallery_Name, p.PK_picture_ID
FROM (tbl_pictures_galleries AS g
INNER JOIN tbl_pictures AS p ON
g.PK_Picture_Gallery_ID = p.FK_picture_galleryID)
INNER JOIN
[SELECT
FK_picture_galleryID, Max(fld_picture_DateTime) AS LastDate
FROM tbl_pictures
GROUP BY FK_picture_galleryID]. AS q
ON p.fld_picture_DateTime =q.LastDate; AND
g.PK_Picture_Gallery_ID = q.FK_picture_galleryID

Are you using an older version of Access? Is that why you put the brackets
around the subquery and followed the closing bracket with a period? With
Access 2000 and later, you don't have to do it that way any more. Simple
parentheses with no period suffice.
If I enter a valid gallery number it
returns the result I'm looking for. My problem is I don't know where
in the query to pass this parameter in ASP.
I have to learn this
sooner of later so I need 2 things.

1) the quick fix: where do I pass the parameter to this query to
provide it with the gallery ID
Assuming you save the query, giving it a name, you can use the technique
showed here:

http://groups.google.com/groups?hl=e...tngp13.phx.gbl
http://groups-beta.google.com/group/...d322b882a604bd

2) the logic to understand it: I need someone to break this down a
little so I can understand it.


Use the Query Builder to create a new query and paste just the subquery into
its SQL View window (again, the table alias is totally unnecessary when your
query contains a single table in the FROM clause - there is no need to
qualify the field names when there is only a single table to choose from):

SELECT FK_picture_galleryID, Max(fld_picture_DateTime) AS LastDate
FROM tbl_pictures
GROUP BY FK_picture_galleryID

Run this query to see what you get. You should see a list of gallery id's
along with the last date a picture was stored for each gallery.
Save the query as qMaxDatePerGallery. Now you have a "table" containing
gallery id's and the last date a picture was stored for each id. Let's
replace that subquery with this "table":

SELECT g.fld_Picture_Gallery_Name, p.PK_picture_ID
FROM (tbl_pictures_galleries AS g
INNER JOIN tbl_pictures AS p ON
g.PK_Picture_Gallery_ID = p.FK_picture_galleryID)
INNER JOIN qMaxDatePerGallery AS q
ON p.fld_picture_DateTime =q.LastDate; AND
g.PK_Picture_Gallery_ID = q.FK_picture_galleryID

Does that help?

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Jul 22 '05 #17

P: n/a
HA!! That's IT!!! GOD, I didn't think I'd EVER get through this. I
have to admit I'm not following it as well as I would like however.
I've got a lot to learn I know. I'll be using this as a "rossetta
stone" until I do.

Bob, wow, thanks man, really, this is really saving my butt!!

Jul 22 '05 #18

This discussion thread is closed

Replies have been disabled for this discussion.