Skip to main content
A
Anonymous
December 9, 2008
Answered

How to do this? results from 2 querys

  • December 9, 2008
  • 14 replies
  • 2479 views
Hi all,

I'm stuck with the following problem.
I need to get all the info from two querys / tables and show them in one table.
I guess I need to do this in a loop or so as I can not do a query in a query.
This is what I have, but does not work as it's always showing the same name.

<cfquery name="getAccounts" datasource="#db2use#">
SELECT *
FROM banners
</cfquery>

<cfoutput query="getAccounts">
<cfset advertPID = #advertPID#>
</cfoutput>

<cfquery name="ListUsers" datasource="#db2use#">
SELECT *
FROM useraccount
WHERE PIDID = '#advertPID#'
</cfquery>

<cfoutput query="ListUsers">
<cfset UserAccount = #name#>
</cfoutput>

<cfoutput query="getAccounts">
name: #name# | advertPID: #advertPID# <br />
</cfoutput>

Both ListUsers and getAccounts have a lot of data.

Anyone here who can show me how to do this?
Thank you.
This topic has been closed for replies.
Correct answer paross1
-- something like this --

<cfquery name="ListUsers" datasource="#db2use#">
SELECT u.name, b.advertPID
FROM useraccount u
INNER JOIN banners b ON u.PIDID = b.advertPID
</cfquery>

<cfoutput query="ListUsers">
name: #name# | advertPID: #advertPID# <br />
</cfoutput>

-- or this --

<cfquery name="ListUsers" datasource="#db2use#">
SELECT u.name, b.advertPID
FROM useraccount u, banners b
WHERE u.PIDID = b.advertPID
</cfquery>

<cfoutput query="ListUsers">
name: #name# | advertPID: #advertPID# <br />
</cfoutput>

Phil

14 replies

N
Newsgroup_User
Inspiring
December 17, 2008
Ian Skinner wrote:
> n03l wrote:
>
>> PS. On my Christmas wish list I've added the "Teach Yourself SQL in
>> 10 Minutes by Ben Forta" book.
>>
>
> If Santa wants to bring you two books, I've also heard good things about
> "Database Design for Mere Mortals" by Michael J. Hernandez

Or mayber "SQL Queries for Mere Mortals" by Michael J. Hernandez, I just
saw this one on Amazon under the listing for "Database Design...".
A
Anonymous
December 17, 2008
As long as it's not one of those "For Dummies" books I'm fine :-)
N
Newsgroup_User
Inspiring
December 17, 2008
n03l wrote:

> PS. On my Christmas wish list I've added the "Teach Yourself SQL in 10 Minutes
> by Ben Forta" book.
>

If Santa wants to bring you two books, I've also heard good things about
"Database Design for Mere Mortals" by Michael J. Hernandez
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
December 17, 2008
On my Christmas wish list I've added the "Teach Yourself SQL in 10 Minutes by Ben Forta" book.

Swell.

A
Anonymous
December 17, 2008
Yes :-)
After putting it on paper 1st (and linking it all together with arrows) I see it now.

Lets hope that this is the last question on this for now from me.

Thank you.

PS. On my Christmas wish list I've added the "Teach Yourself SQL in 10 Minutes by Ben Forta" book.
A
Anonymous
December 17, 2008
Still being on this subject why does this cfquery not work (hangs Coldfusion)
Note the already set advertPIDSET.
The 12345 does exist in the table useraccount / PIDID.

<cfset advertPIDSET = "12345">
<cfquery name="ListUsers" datasource="#db2use#">
SELECT u.name, b.advertPID
FROM useraccount u, banners b
WHERE u.PIDID = #advertPIDSET#
</cfquery>

<cfoutput query="ListUsers">
name: #name# | advertPID: #advertPID# <br />
</cfoutput>

I also tested: WHERE u.PIDID = "#advertPIDSET#"
and: WHERE u.PIDID = '#advertPIDSET#'
with the same 'bad' results.


This does work fine, but again only with results from one table.

<cfset advertPIDSET = "12345">
<cfquery name="ListUsers" datasource="#db2use#">
SELECT *
FROM useraccount
WHERE PIDID = '#advertPIDSET#'
</cfquery>

<cfoutput query="ListUsers">
<cfset UserAccount = #name#>
</cfoutput>


Thank you for any info.
-
-__cfSearching__-
Inspiring
December 17, 2008
> why does this cfquery not work (hangs Coldfusion)
> SELECT u.name, b.advertPID
> FROM useraccount u, banners b
> WHERE u.PIDID = #advertPIDSET#

You forgot to join on the related column, as in paross1's example (ie ON u.PIDID = b.advertPID). So the result is a cartesian product (ie the number of records in the Useraccount table x number of records in the Banners table) In other words a potentially large (very large) resultset. ColdFusion may be hanging due to the volume of information returned. Add the join(s) on the related column(s) and it should fix the issue.


A
Anonymous
December 17, 2008
Hi Ian,

Thank you for the info.
I was already thinking this.

Again. The BKBK way "looks" more logical too me, and I might use it one day if I'm really stuck with an impossible (for me) "cfquery".

Or... I might post the "impossible cfquery" on this forum again where many people will help.

Once again thank you all.
N
Newsgroup_User
Inspiring
December 16, 2008
n03l wrote:
> BKBK,
>
> Thank you for your input.
>
> As Phil's way is working great for me, I do not need to change it again.
> However. Your suggestion looks more logical to me!

It may seem more logical, but doing it in the database with the
INNER|OUTER|CROSS JOIN is more preformant. As a general rule, it is
better to let the database engine do the heavy data manipulation work.
Much money and brain power has gone into making them as efficient as
possible at it.
A
Anonymous
December 16, 2008
BKBK,

Thank you for your input.

As Phil's way is working great for me, I do not need to change it again.
However. Your suggestion looks more logical to me!
BKBK
Community Expert
BKBKCommunity Expert
Community Expert
December 14, 2008
Alternatively, you could fetch the data in two separate queries, like this

<cfquery name="getAccounts" datasource="#db2use#">
SELECT *
FROM banners
</cfquery>

<!--- the list of IDs --->
<cfset advertPIDList = valuelist(getAccounts.advertPID)>

<!--- place each ID between single quotes --->
<!--- (assuming advertPID to be a string) --->
<cfset qualifiedIDList = ListQualify(advertPIDList, "'", ",", "CHAR")>

<cfquery name="ListUsers" datasource="#db2use#">
SELECT *
FROM useraccount
WHERE PIDID IN (#preserveSingleQuotes(qualifiedIDList)#)
</cfquery>

I've added this just to confirm that you were originally on the right track. However, Phil's suggestion is more efficient. It fetches data from both tables in one, single query.

A
Anonymous
December 14, 2008
Phil,
Both ways are working great!
I did not know about the "INNER JOIN" option!

Once again thank you.
Show more replies
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices