Solved

cfquery and two queries in that

Posted on 2011-02-10
18
605 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
Question by:myselfrandhawa
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 22

Expert Comment

by:plusone3055
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
>  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
Comment Utility
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
Comment Utility
> 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
Comment Utility
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
Comment Utility

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

 Why do you want to do this?  


  Please explain ....
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Expert Comment

by:gdemaria
Comment Utility

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

Expert Comment

by:Brijesh Chauhan
Comment Utility
> 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
Comment Utility

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 15

Author Comment

by:myselfrandhawa
Comment Utility
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
Comment Utility
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
Comment Utility
> 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
Comment Utility
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
Comment Utility
that is still only one query
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

18 Experts available now in Live!

Get 1:1 Help Now