Select Within a Select

<cfquery datasource="#application.datasource#" name="qGalleries">
SELECT *
FROM galleries
AS g
LEFT JOIN galleryImages
AS i
ON g.galleryID = i.imageGalleryID
ORDER BY galleryID
ASC
</cfquery>

Something like that. Then you might do:

<ul class="options">
<cfoutput query="qGalleries" group="galleryID">
<li><a href="editGallery.cfm?galleryId=#galleryId#">#galleryName#</a>
<ul>
<cfoutput group="imageID">
<li><a href="">#imageName#</a></li>
</cfoutput>
</ul>
</li>
</cfoutput>
</ul>

It's not exact, but is a rough example. It's not tested or anything.

Oh and, do you REALLY need to use * in your select? It gets all columns and rows. If you don't need all columns from both tables then define exactly what columns you need.

Mikey.

If you are able to JOIN the tables, then you can use the nest <cfoutput> tag
to get the required result.

--
Prasanth Kumar.S
"-==cfSearching==-" <webforumsuser@macromedia.com> wrote in message
news:gip1l4$o19$1@forums.macromedia.com...
> Hulfy wrote:
> > What is the best way achieve this a nested select?
>
> Not a nested select. What you need is a JOIN between the two tables. A
> JOIN
> lets you retrieve information from both tables in the same query. You can
> then
> use the aggregate COUNT function to get the total number of matching
> records in
> your second table. If you are not familiar with JOINs, I would suggest
> reviewing an sql tutorial such as:
>
> http://www.w3schools.com/sql/sql_join.asp
> http://www.w3schools.com/sql/sql_func_count.asp
>
>

Hulfy wrote:
> What is the best way achieve this a nested select?

Not a nested select. What you need is a JOIN between the two tables. A JOIN lets you retrieve information from both tables in the same query. You can then use the aggregate COUNT function to get the total number of matching records in your second table. If you are not familiar with JOINs, I would suggest reviewing a sql tutorial such as:

http://www.w3schools.com/sql/sql_join.asp
http://www.w3schools.com/sql/sql_func_count.asp