Note also that the subquery used this way must be a scalar subquery.  T-SQL
will throw an error if it is not (i.e. the subquery returns more than one
row), and he may actually be getting an error instead of a resultset with no
rows...

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Brian Freeman [mailto:freeman@C...]
Sent: Thursday, October 03, 2002 8:46 AM
To: sql language
Subject: [sql_language] RE: Subquery confusion


Your "subquery" erroneously references the main query table (line is marked)
(you may have been meaning to correlate them or just used substrans when you
meant fpayments).

select substrans.cpkey,
    (select cfprchqno from fpayments
    where fpayments.cfprmemno = '00011'
-->    and substrans.csubsinvtype = 'PAY')
as thechk
from substrans where substrans.csubmemno = '00011';

You need to either use a separated subquery i.e.

select substrans.cpkey,
(select cfprchqno form fpayments inner join substrans on
fpayments.field=substrans.field)
as thechk
from substrans where substrans.csubmemno = '00011';

or correlate the subquery:

select substrans.cpkey,
(select cfprchqno form fpayments
    where fpayments.cfprmemno = '00011'
	and fpayments.field=substrans.field)
as thechk
from substrans where substrans.csubmemno = '00011';

Brian Freeman
Carnegie Technology and Bluewave Computing
(xxx) xxx-xxxx  ext. 415
www.carnegie.com <http://www.carnegie.com>  / www.bluewave-computing.com
<http://www.bluewave-computing.com>



-----Original Message-----
From: Alan Reinhart [mailto:avres@c...]
Sent: Thursday, October 03, 2002 8:33 AM
To: sql language
Subject: [sql_language] Subquery confusion


Wondering why the following is happening:

In this simple select, 4 rows are returned:

select substrans.cpkey from substrans where substrans.csubmemno = '00011';
Row 1:
   CPKEY = 1017830
 Row 2:
   CPKEY = 1017831
 Row 3:
   CPKEY = 1017832
 Row 4:
   CPKEY = 1017833

If I expand the select with an expression subquery (as below) to
"conditionally" include an extra column, NOTHING is returned! I'm expecting
a return that looks like the above with the extra 'thechk' column, and when
the subquery returns nothing, NULL would be in 'thechk' column.


select substrans.cpkey,
    (select cfprchqno from fpayments
    where fpayments.cfprmemno = '00011'
    and substrans.csubsinvtype = 'PAY')
as thechk
from substrans where substrans.csubmemno = '00011';

Shouldn't the above ALWAYS return at least a table with the 4 CPKEY rows?

=Alan R.