Reference to Table Alias in From Clause to be Used by Subquery inSelect Clause

I am using 5.0.26-NT on Windows 2000.

I have need to use a reference in the outer from clause in a subquery in
the select clause. Consider the following example:

Select (select b.baitID from b where b.entrydate > curdate()) as
wantedBaitIDs from bait_tbl b;

My actual need is more complex than this as part of it is a rough cross
tab. If I try to define the table in the alias, not only do I lose
whatever benefits there are in the particular join I would use in the
outer from clause but would also require the join to be defined in each
subquery, requiring it to be examined each time it is used.

To be absolutely clear, in this example I want to use bait_tbl with the
alias of b in the subquery. In my actual query I reference the same
table twice with a different join set for each. I need to reference a
particular alias as that has the join set I need.

Re: Reference to Table Alias in From Clause to be Used by Subqueryin Select Clause

No bother wrote:
> I am using 5.0.26-NT on Windows 2000.
>
> I have need to use a reference in the outer from clause in a subquery in
> the select clause. Consider the following example:
>
> Select (select b.baitID from b where b.entrydate > curdate()) as
> wantedBaitIDs from bait_tbl b;
>
> My actual need is more complex than this as part of it is a rough cross
> tab. If I try to define the table in the alias, not only do I lose
> whatever benefits there are in the particular join I would use in the
> outer from clause but would also require the join to be defined in each
> subquery, requiring it to be examined each time it is used.
>
> To be absolutely clear, in this example I want to use bait_tbl with the
> alias of b in the subquery. In my actual query I reference the same
> table twice with a different join set for each. I need to reference a
> particular alias as that has the join set I need.

I just realized that I was going about the whole thing wrong, though the
answer to this question might still prove useful to someone else, or
possibly me in the future.

Insufficient sleep + insufficient caffeine -> inappropriate posts to
technical newsgroups.

I beg the indulgences of the community here.

Re: Reference to Table Alias in From Clause to be Used by Subqueryin Select Clause

No bother wrote:
> Select (select b.baitID from b where b.entrydate > curdate()) as
> wantedBaitIDs from bait_tbl b;

Does something like this do what you want?

SELECT IF(b.entrydate > curdate(), b.baitID, NULL) AS wantedBaitIDs
FROM bait_tbl AS b;

Can you be more specific about the more complex case? It's hard to
visualize what you're describing unless we know the join conditions, etc.


Regards,
Bill K.

Re: Reference to Table Alias in From Clause to be Used by Subqueryin Select Clause

Bill Karwin wrote:
> No bother wrote:
>> Select (select b.baitID from b where b.entrydate > curdate()) as
>> wantedBaitIDs from bait_tbl b;
>
> Does something like this do what you want?
>
> SELECT IF(b.entrydate > curdate(), b.baitID, NULL) AS wantedBaitIDs
> FROM bait_tbl AS b;
>
> Can you be more specific about the more complex case? It's hard to
> visualize what you're describing unless we know the join conditions, etc.
>
>
> Regards,
> Bill K.

No, I specifically thought I needed to use a subquery.

Select
max(if(b.baittype = 'Worms',(select count(b.baitID) where b.entrydate <
curdate(),0) as 'Worms',
max(if(b.baittype = 'Lure', (select count(b.baitID) where b.entrydate >
curdate(),0) as 'Lure',
max(if(b.baittype = 'Other',(select count(b.baitID) where
b.baitDistributor = 'A Retailor Near You'),0) as 'Other'
from bait_tbl b inner join customer_tbl c on b.baitID = c.PreferedBaitID

As you can see the specific criteria to be used depends on the baittype.

All I really want to know is if I can refer to an alias so designated in
the outmost from clause from within a subquery in the select clause. In
the first example I gave the alias is defined outside of the subquery.
Mysql does not recognize that the table alias in the subquery means the
table outside of the subquery.

Re: Reference to Table Alias in From Clause to be Used by Subquery in Select Clause

No bother wrote:
> All I really want to know is if I can refer to an alias so designated in
> the outmost from clause from within a subquery in the select clause.

Yes you can, it's called a correlated subquery when you do that.

But you can't do what you're doing, which is a SELECT with a COUNT() and
a WHERE clause, but no FROM clause.

Maybe this will help: the alias b is not an alias for the whole table
bait_tbl. It's an alias to each individual row in bait_tbl. So during
a given expression evaluation, b refers to only one row. You can
compare columns of that one row to corresponding columns queried in a
subquery, and they'll be compared row by row.

I wish I could suggest something more substantial, but I still can't
tell from your example what you want the output to be.

Regards,
Bill K.

Re: Reference to Table Alias in From Clause to be Used by Subqueryin Select Clause

Bill Karwin wrote:
> No bother wrote:
>> All I really want to know is if I can refer to an alias so designated
>> in the outmost from clause from within a subquery in the select clause.
>
> Yes you can, it's called a correlated subquery when you do that.
>
> But you can't do what you're doing, which is a SELECT with a COUNT() and
> a WHERE clause, but no FROM clause.
>
> Maybe this will help: the alias b is not an alias for the whole table
> bait_tbl. It's an alias to each individual row in bait_tbl. So during
> a given expression evaluation, b refers to only one row. You can
> compare columns of that one row to corresponding columns queried in a
> subquery, and they'll be compared row by row.
>
> I wish I could suggest something more substantial, but I still can't
> tell from your example what you want the output to be.
>
> Regards,
> Bill K.

I was hoping you wouldn't say that. I think I understand now.