Solved

cfquery and two queries in that

Posted on 2011-02-10
18
617 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
  • 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 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