cfquery and two queries in that

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>

Select allOpen 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>

Select allOpen in new window

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>

Select allOpen 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

Select allOpen in new window

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>

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

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