File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes albums and photos Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "albums and photos" Watch "albums and photos" New topic
Author

albums and photos

Brian Percival
Ranch Hand

Joined: Jun 23, 2004
Posts: 163
I am sure there is a standard solution for this.

I am using ms-sql server

I have tblalbums, tblphotos

albums have photos

for an album there can be one photo that is featured (featured=1 in tblphotos)

I need to display a page where I show each album name with its number of photos and the featured photo.

what is the best sql for this?

I cann't have storedprocedures.

thanks for your help.

Brian
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29249
    
139

Brian,
Even without stored procs, you can do multiple queries. I recommend one query to get all the albumn names with the corresponding # of photos and another query to get the featured photo for each album. You could do it in one query, but it would be complex.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
gao zhixin
Ranch Hand

Joined: Jan 18, 2006
Posts: 42
but two query is effectiveless... I think the best way it to use some feature to maintain a table where has a field contain the #, Ex. trigger?
I 'm not skilled in DB, so you can ignore my suggest...
Brian Smith
Ranch Hand

Joined: May 20, 2005
Posts: 63
This may not be most best solution but it's easy. Since you are using a 1 for featured photo (and i am assuming a 0 for all non-featured) just use an Order By DESC clause to order the photos, the first will be your featured photo, then you can just count the records in the result set by iterating through it.
[ April 21, 2006: Message edited by: Brian Smith ]
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29249
    
139

Originally posted by gao zhixin:
but two query is effectiveless...

Why is that?
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: albums and photos
 
Similar Threads
Photo Album in JSP
What's your reason to like & dislike Apple?
Hyperlinks
Buying Smart Electronics
Google Picasa - Photo Selection