Solved

cfquery and two queries in that

Posted on 2011-02-10
18
620 Views
Last Modified: 2012-05-11
Hi, Somebody asked me how he can run two different select querues in the cfquery tag and how he can fetch the result of the second query!

is this possible, plz do let me know

Thanks
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 22

Expert Comment

by:plusone3055
ID: 34864608
well you can just make two separate <cfquery> tages  

EXample
<cfquery datasource ="XXX" name="SQL1">
XXXX XXXX XX
</cfquery>

<cfquery datasource ="XXX" name="SQL2">
XXXX XXXX XX
</cfquery>


and output both
<CFOUTPUT QUERY="SQL1">
XXXXXX
XXXX
<CFOUTPUT QUERY="SQL2">
XXXX XXXXXX
</cfoutput>
</cfoutput>

OR
Just do a subquery in SQL1
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34864630
Use Subqueries --not sure if this is what you are looking for

Example

SELECT name
FROM   customer
WHERE  customer_id NOT IN
(
  SELECT customer_id
   FROM salesorder
 );
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34864658
can run two different select querues in the cfquery tag  

<cfquery name="test" datasource="dsn"> 
SELECT name 
        FROM   employee 
        WHERE  employee_id = ANY ( 
                               SELECT employee_id 
                               FROM   salesorder 
                               WHERE  order_date = '7/19/1994' 
                              );  
</cfquery>

Open in new window

 

<cfquery name="test" datasource="dsn"> 
SELECT name 
        FROM   employee 
        WHERE  EXISTS ( 
                               SELECT employee_id 
                               FROM   salesorder 
                               WHERE  salesorder.employee_id = employee.employee_id AND 
                                      order_date = '7/19/1994' 
                              );   
</cfquery>

Open in new window

0
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34864677
no u all are taking me wrong/!

like this i asked

<cfquery name="test" datasource="dsn">
SELECT name
        FROM   employee;
select * from dept
</cfquery>

now i want to fetch the results of department table!

how do i do that
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34864727
>  how do i do that


you  just did it.  

That's is how you could do it.   But keep in mind that you won't see the results of the first query at all, only the second.


But you need to explain WHY you want to do this.  THere is no reason to have the first query there because you get nothing from it.   If you are clear about your objective, we can tell you how to do what you want to do.

0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34864756
NO I don't think you can write two select statements, but you can again use subqueries, something like below

<cfquery name="test" datasource="dsn"> 
select d.deptname, d.deptlocation,
(select e.name from employee e) as empName
from dept d
</cfquery>

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34864973
> NO I don't think you can write two select statements

Yes, You can, but I was mistaken.  You get the results of the first query, not the second.


People typically combine multiple SQL statements into one CFQUERY when only ONE of them is a select statement, the others are INSERTs and UPDATEs or DELETEs.  

So, again, why would you want to do this?   What is your objective?

Unless you really just want to combine the queries using a join...

SELECT e.name 
     , d.*
FROM   employee e
  left join dept where e.dept_id = d.dept_id

Open in new window

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 167 total points
ID: 34865863
Yes, You can, but I was mistaken.  You get the results of the first query, not the second.

The one logical exception I can think of is if maybe they wanted to do a UNION.  But with the table names "employee" and "Dept" that does not seem likely ;-)

SELECT ColA, ColB FROM FooTable
UNION
SELECT ColA, ColB FROM BarTable

Open in new window


0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34866564

It's a mystery, myselfrandhawa inquiring minds want to know...

 Why do you want to do this?  


  Please explain ....
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34868047

I'm dying to know what you are trying to do..
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34868085
> Yes, You can, but I was mistaken.  You get the results of the first query, not the second.

If you are Not getting the results of the second query, which probably means that it is NOT even executed, it does not help to put more than one select statements the way author has added, semicolon is an end of select identifier in quries, so nothing after that would be excecuted.

As from above posts by gdermia, agx, you can either use subqueries, Unions, Joins to get the desired results.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34868120

You can put multiple SQL statements inside a CFQUERY.

If you doubt it, you need to simply try it.  I do it all the time.

But you are correct, as we said already, there is no point to doing multiple SELECT statements because you are not getting back the results of both queries.   So it is pointless.

>  semicolon is an end of select identifier in quries, so nothing after that would be excecuted.

Not true.  The next statement will be executed.

Try it....


create table temp ( myCol varchar(30));

<cfquery name="JustTryIt" ....>
   insert into temp (myCol) values ('One');
   insert into temp (myCol) values ('Two');
   insert into temp (myCol) values ('Three');
   insert into temp (myCol) values ('Four');
   update temp set myCol = 'Updated' where myCol = 'Four'
   select * from temp;
</cfquery>

Open in new window

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 34868222
i was asked this question when i was once interviewed, i told him no it can't be but he said yes it can be achieved and how that i do not know
0
 
LVL 11

Expert Comment

by:Brijesh Chauhan
ID: 34868259
i was asked this question when i was once interviewed, i told him no it can't be but he said yes it can be achieved and how that i do not know

You should have asked him to give you an example, but I guess he was looking for probably Subqueries or Unions or Join statements.....

@gdemaria, thanks for the information, it works !

0
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 167 total points
ID: 34868312
> i was asked this question when i was once interviewed

Too bad that you didn't find out what he was thinking.

It also depends on who he asked the question.  If he said two SQL statements, that is very different from two SELECT statements.

You can also two SELECTs inside a SQL Block, this will return the 2nd SELECT

Declare @ID INT
BEGIN
   select @ID = categoryID from categories where catName = 'Fish'

  select * from allProducts where categoryID = @ID
END


I don't think it could be a subquery or Union or Join, because the question asked How do you get the result of the SECOND query.   If you merged the two queries together using one of those methods, there would not be two queries to get the 2nd one from...

I guess it's remains a mystery
0
 
LVL 11

Assisted Solution

by:Brijesh Chauhan
Brijesh Chauhan earned 166 total points
ID: 34868343
Well Only the guy who asked would know what exactly he was looking for.. you can also do (ORACLE)

select x.*, y.*
from
(SELECT count(*) FROM database.USAGE WHERE SERVICE_TYPE = 'C' AND ACCOUNT = '4837') x,
(SELECT sum(minutes) FROM database.USAGE WHERE SERVICE_TYPE = 'DA' AND ACCOUNT = '4837') y;


0
 
LVL 39

Expert Comment

by:gdemaria
ID: 34868363
that is still only one query
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34868412
i was asked this question when i was once interviewed, i told him no it can't be but he said yes it can be achieved and how that i do not know

Like gdemaria said, it depends on the context.  

But it all sounds dubious ...  cfquery's are designed to return one, and only one, query object.  So if a group of sql statements returned multiple results, you'd have to use a stored procedure to get more than the 1st result.  I doubt even the old <cfquery>{ call } </cfquery> hack could return multiple results.  Not from cfquery anyway.  

(I'm sometimes appalled at the masses of sql some people try and jam into a single cfquery. Jeez .. just use a stored procedure already! That's what they're there for .. )

0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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