left join won't work in query of query?

Another way. Don't use q of q at all.

q1
select distinct id from dept

q2
select id, name, coalesce(text, '') isthere
from employee left join
( select distinct id, 'yes' text
from employee
where id in (#valuelist(q1.id#)
) x on employee.id = x.id

This took a bit of trial and error and there where some interesting
gotcha's along the way. But this might get you the results you want
within the capabilities of QofQ.

<cfscript>
aQry = queryNew('id,color');
bQry = queryNew('id,flavor');

queryAddRow(aQry,3);
queryAddRow(bQry,2);

querySetCell(aQry,'id',1,1);
querySetCell(aQry,'id',2,2);
querySetCell(aQry,'id',3,3);

querySetCell(aQry,'color','red',1);
querySetCell(aQry,'color','green',2);
querySetCell(aQry,'color','blue',3);

querySetCell(bQry,'id',1,1);
querySetCell(bQry,'id',2,2);

querySetCell(bQry,'flavor','grape',1);
querySetCell(bQry,'flavor','orange',2);
</cfscript>

<cfdump var="#aQry#">
<cfdump var="#bQry#">

<cfquery name="combo" dbtype="query">
select aQry.id,aQry.color,bQry.flavor
from aQry, bQry
where aQry.id = bQry.id

UNION ALL

select id,color,'null'
from aQry
where id NOT IN (#valueList(bQry.id)#)
</cfquery>

<cfdump var="#combo#">
</body>

It sure would be nice if one could just use a simple outer join to get
the same results.

thanks Dan,

i want to get result like this...

my both tables are in different datasourse. One sybase, one in SQL.

Employee Table
id name
1051 John
1292 Sam
1332 Mary
1252 Joe
1272 Sue
1492 Peter




Dept Table
id
1292
1332
1492




Result
id name dept
1051 John
1292 Sam yes
1332 Mary yes
1252 Joe
1272 Sue
1492 Peter yes

You can't use the word "join" at all in Q of Q. Looking at your code, it's not clear what you are trying to accomplish.

Answered in other thread.