Ok, I admit I am over my head.
I need to query a table, episodes, returning ptNum, epiNum, ptStmtDir
for all rows that have had a transaction in the given month/year.

I have the subquery working, it is a union of 3 selects which return
ptNum and EpiNum.
Here is the final select that errors: (from a php script)
-----------------------------------------------
select ptNum,
epiNum,
ptStmtDir
from episodes
where (ptnum, epiNum) in
(select ptNum,
epiNum
from transactions
where postingDate between
cast('$firstdate' as date)
and
cast('$lastdate' as date))
union
(select ptNum,
epiNum
from paymentDistribution
join payment on
paymentDistribution.paymentDNumber = payment.paymentNumber
where verifyDate between
cast('$firstdate' as date)
and
cast('$lastdate' as date))
union
(select ptNum,
epiNum
from beginningBalances
where date between cast('$firstdate' as date)
and
cast('$lastdate' as date))
---------------------------------------------------------------------------------
The error message when I execute the statement is:
"The used SELECT statements have a different number of columns"
I suspect that I am using the wrong syntax in the first where
statement.
BTW, I would welcome suggestions about making this more efficient - it
is not a big deal as this script runs once a month in the middle of
the night and the tables referred to in the subquery have only about
10,000 rows each.

On 2010-07-04 13:24, bill wrote:

Your first part is not union compatible with the two latter ones. If you
don't have something that corresponds to episodes.ptStmtDir, you can add
a dummy placeholder as:

cast(null as <typeof episodes.ptStmtDir>)

in the other two.

As for efficiency, mysql does not seem to handle in predicates very
well. It is often suggested to replace it with a join where possible:

select ptNum, epiNum, ptStmtDir
from episodes
where (ptnum, epiNum) in (
select ptNum, epiNum
from transactions
where postingDate between cast('$firstdate' as date)
and cast('$lastdate' as date)
)

would then become:

select x.ptNum, x.epiNum, x.ptStmtDir
from episodes x
join transactions y
on (x.ptnum, x.epiNum) = (y.ptnum, y.epiNum)
where y.postingDate between cast('$firstdate' as date)
and cast('$lastdate' as date)



/Lennart

On 2010-07-04 14:11, Lennart Jonsson wrote:
Wait a minute, I did not read your text carefully enough. Your problem
probably lies in the closing ) of the in predicate:


select ptNum, epiNum, ptStmtDir
from episodes
where (ptnum, epiNum) in (
select ptNum, epiNum
from transactions
where postingDate between cast('$firstdate' as date)
and cast('$lastdate' as date)

-- BEGIN: Should this really be here?
)
-- END:

union

I'd say this is easier to spot with indented code, agreed ;-)


However, try avoiding the in predicate. Your union query is a derived
table, i.e. you can join that as with any other table.

On Jul 4, 8:19*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
That paren closes the first select that is unioned.

The 3 later selects form the subquery, each unioned to the prior one.
The code after the "in" runs fine by itself.

On Jul 4, 8:11*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
The problem with a join (from my inexperienced point of view) is that
I need the episode records that match the ptNum and epiNum of any of
the records from transactions or paymentDirs or beginningBalances.

On Jul 4, 8:19*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
How would I join the union derrived table, by putting parens around
the union selects ?
bill

On 2010-07-04 15:29, bill wrote:
Let's start by indenting the code:

select ptNum, epiNum, ptStmtDir
from episodes
where (ptnum, epiNum) in (
select ptNum, epiNum
from transactions
where postingDate between cast('$firstdate' as date)
and cast('$lastdate' as date)
)

union

select ptNum, epiNum
from paymentDistribution
join payment
on paymentDistribution.paymentDNumber = payment.paymentNumber
where verifyDate between cast('$firstdate' as date)
and cast('$lastdate' as date)

union

select ptNum, epiNum
from beginningBalances
where date between cast('$firstdate' as date)
and cast('$lastdate' as date)


Do you see a problem here? The result of first part of your query has
type - say - int * int * string, whilst second and third part has type
int * int. I.e. part 2 and 3 are not union compatible with part 1, which
is what mysql is complaining about.

In my first answer I suggested that you make the second and third part
union compatible via:

[...]

union

select ptNum, epiNum, cast(null as ...)
from paymentDistribution
join payment
on paymentDistribution.paymentDNumber = payment.paymentNumber
where verifyDate between cast('$firstdate' as date)
and cast('$lastdate' as date)

union

select ptNum, epiNum, cast(null as ...)

[...]

But when I reread your post I was pretty sure that what you meant was:

select ptNum, epiNum, ptStmtDir
from episodes
where (ptnum, epiNum) in (
select ptNum, epiNum
from transactions
where postingDate between cast('$firstdate' as date)
and cast('$lastdate' as date)
union

select ptNum, epiNum
from paymentDistribution
join payment
on paymentDistribution.paymentDNumber = payment.paymentNumber
where verifyDate between cast('$firstdate' as date)
and cast('$lastdate' as date)

union

select ptNum, epiNum
from beginningBalances
where date between cast('$firstdate' as date)
and cast('$lastdate' as date)

)

Before we continue this discussion, we need to straighten out which
query to continue with.

/Lennart






When I reread your post

On 2010-07-04 15:32, bill wrote:
[...]
I'll answer this once we have established what it is you are trying to
do, see my other reply.

/Lennart

On Jul 4, 10:39*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
The examples in the documentation show each select that is unioned
contained withing parentheses:
eg
(select....)
union
(select...)

so, staying with this convention what I am trying to do is: (peudo-
code)

select from episodes.... where (ptNum, epiNum) in
(
(select from transactions...)
union
(select from payments ...join paymentDistributions)
union
(select from beginingBalances...)
)

in words:
if there are any transactions or payments or beginningBalances this
month, give me the episode data.

bill

On Jul 4, 12:14*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
BTW: I very much appreciate the time you are spending disentangling my
code and intent.
I think that the 2nd example is what I intend - but the parens in the
examples is confusing.
bill