Select Within a Select

[Hulfy]

Hi,

I am using the code below to output all the names of my image galleries. Now I want to output the number of images in the galleries(my image table is called galleryImages). What is the best way achieve this a nested select?

Any help would be appreciated.

[-__cfSearching__-]

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

[Newsgroup_User]

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
>
>

[Kapitaine]

<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.

[-__cfSearching__-]

Kapitaine wrote:
> <cfoutput query="qGalleries" group="galleryID">

If all they need is the total of the number of images, then a nested cfoutput is not necessary, just COUNT. But obviously that, and the type of JOIN, depends on the desired results. So it may be a good idea for them to review the sql tutorials to help them understand what sql is actually needed.