Link to home
Create AccountLog in
Avatar of Coast Line
Coast LineFlag for Canada

asked on

cfquery and two queries in that

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
Avatar of plusone3055
plusone3055
Flag of United States of America image

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
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
 );
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

Avatar of Coast Line

ASKER

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
>  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.

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

> 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

ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

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

 Why do you want to do this?  


  Please explain ....

I'm dying to know what you are trying to do..
> 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.

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

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
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 !

SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
that is still only one query
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 .. )