Replace In the output query with values from another table

Posted on 2008-10-25
Last Modified: 2013-12-16
Hello experts.
In the code below i want to replace the extras values with the values Extra_text from the Extras_All table
but i need only the first 2 separated with (, ) and if there are more than 2 i want to display in the end (....) to show that there are more than 2.
For in formation about the queries take a look at this question:
Any help?

Artikel_ID        Extras                                

 2                     10

 2                     20

 2                     30                    

 5                     20

 5                     30


 a second

Extras_All :    

Extras_ID       Extras_Text                                

10                   text1

20                   text2

30                   text3

A third artikel

art_ID      Price  

2             10

5             15

In the above tables artikel.art_ID=ArtExtras.Artikel_ID

    and             Extras_all.Extras_ID=ArtExtras.Extras


<cfoutput query="dboartikel" group="#url.tfm_orderby#" startRow="#StartRow_dboartikel#" maxRows="#MaxRows_dboartikel#">

<cfoutput group="extras">


  <cfif #Extras# NEQ "">




Open in new window

Question by:Panos
  • 3
LVL 16

Accepted Solution

duncancumming earned 500 total points
ID: 22825974
Something like this?  Some words of warning:
1.  I'm not sure of the need for the url.tfm_orderby, but I've kept it in because that's what you've got
2.  If using that in url parameter in your ORDER BY statement, use a cfqueryparam around it
3.  Try and avoid using startrow and maxrows when grouping your cfoutput, it'll just cause confusion.  Better to try and find some other way (i.e. don't use the group attributes, write your own logic for working it out)
4.  The method I've used for building up a string as a list then looping through just the first 2 elements, etc, isn't ideal
<cfquery name="getArtikel">

	SELECT A.art_ID, A.price, E.Extras_ID, E.Extras_Text

	FROM Extras_All E

			INNER JOIN ArtExtras AE ON AE.Extras = E.Extras_ID

			INNER JOIN Artikel A ON AE.Artikel_ID = A.Art_ID

	ORDER BY #url.tfm_orderby#, A.art_ID, E.Extras_ID


<cfset ExtraText = "">

<cfoutput query="getArtikel" group="#url.tfm_orderby#">

	<cfoutput group="art_ID">



			<cfset ExtraText = ListAppend(ExtraText, getArtikel.Extras_Text)>



		<cfloop index="i" from="1" to="#Min(2, ListLen(ExtraText))#">

			#ListGetAt(ExtraText, i)##

			<cfif ListLen(ExtraText) GT 1 AND i EQ 1>, </cfif>


		<cfif ListLen(ExtraText) GT 2>...</cfif>



Open in new window


Author Comment

ID: 22829428
Hi duncancumming.
Thank you for your help.
Here is one more question that has to do with this one so that you can have more details:

The first mistake is that i have FROM artikel a.It must appending your code FROM Extras_ALL.
Now i try to use your code in this example page but i have error
Column A.Art_ID is invalid in theselect list because it is noy contained in either an aggregate function or the group by close.

Author Comment

ID: 22829541
Sorry for the error.
I did a mistace.
Now the code is giving results but it is not working good

Author Closing Comment

ID: 31509925

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When setting up new project requests for our site, one of the most powerful tools our team has available to use is Axure ( It’s a tool for creating software and web prototypes that can function and interact as if it were the a…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following:…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be Log into your Hosting account. IPage will be used for demonstration : Locat…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now