subquery using IN with 2 values

#11

On 2010-07-04 19:00, bill wrote:

Quote:

On Jul 4, 10:39 am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com
wrote:
On 2010-07-04 15:29, bill wrote:

On Jul 4, 8:19 am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com
wrote:
On 2010-07-04 14:11, Lennart Jonsson wrote:

On 2010-07-04 13:24, bill wrote:
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))

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:

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

That paren closes the first select that is unioned.

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.

The examples in the documentation show each select that is unioned
contained withing parentheses:
eg
(select....)
union
(select...)

Yes, but you have a closing paranthes that moves the union to the
outmost level,

select ... from episodes
where (ptnum, epiNum) in (
...
)
union
(select ...)
union
(select ...)

You need to change that to:

select ... from episodes
where (ptnum, epiNum) in (
...
union
(select ...)
union
(select ...)
)

I'll assume that this query works and that retrieves the expected
result. From my limited experience with mysql, in predicates are
something to stay away from. Given that I would rewrite this query to:

select ptNum, epiNum, ptStmtDir
from episodes e
join (

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)

) x
(e.ptnum, e.epiNum) = (x.ptnum, x.epiNum)

x is a derived table that you can join episodes with. You might even try:

select ptNum, epiNum, ptStmtDir
from episodes e
join (

select ptNum, epiNum, postingDate as somedate
from transactions

union

select ptNum, epiNum, verifyDate as somedate
from paymentDistribution
join payment
on paymentDistribution.paymentDNumber = payment.paymentNumber

union

select ptNum, epiNum, date as somedate
from beginningBalances

) x
(e.ptnum, e.epiNum) = (x.ptnum, x.epiNum)
where somedate between cast('$firstdate' as date)
and cast('$lastdate' as date)

But I don't think the mysql optimizer will understand how to push the
between predicate inside x, and then this will be very inefficient.

Just curious, how does the in query compare with the join query
regarding executiontime?

/Lennart

#12

On 2010-07-04 19:02, bill wrote:
[...]

Quote:

BTW: I very much appreciate the time you are spending disentangling my
code and intent.

You're welcome

Quote:

I think that the 2nd example is what I intend - but the parens in the
examples is confusing.

Your parens or mine ;-) See my other post

/Lennart

#13

....

Quote:

Yes, but you have a closing paranthes that moves the union to the
outmost level,

select ... from episodes
where (ptnum, epiNum) in (
* *...
)
union
(select ...)
union
(select ...)

You need to change that to:

select ... from episodes
where (ptnum, epiNum) in (
* *... <<<--------------doesn't this select require parens around it ?
* *union
* *(select ...)
* *union
* *(select ...)
)

I'll assume that this query works and that retrieves the expected
result. From my limited experience with mysql, in predicates are
something to stay away from. Given that I would rewrite this query to:

select ptNum, epiNum, ptStmtDir
from episodes e
join (

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

) x
* * (e.ptnum, e.epiNum) = (x.ptnum, x.epiNum)

x is a derived table that you can join episodes with. You might even try:

select ptNum, epiNum, ptStmtDir
from episodes e
join (

* * select ptNum, epiNum, postingDate as somedate
* * from transactions

* * union

* * select ptNum, epiNum, verifyDate as somedate
* * from paymentDistribution
* * join payment
* * * * on paymentDistribution.paymentDNumber = payment.paymentNumber

* * union

* * select ptNum, epiNum, date as somedate
* * from beginningBalances

) x
* * (e.ptnum, e.epiNum) = (x.ptnum, x.epiNum)
where somedate between cast('$firstdate' as date)
* * * * * * * * * *and cast('$lastdate' as date)

I can't do this as it retrieves one record per transaction instead of
one record per ptNum,epiNum pair.

Quote:

But I don't think the mysql optimizer will understand how to push the
between predicate inside x, and then this will be very inefficient.

Just curious, how does the in query compare with the join query
regarding executiontime?

Great question, but as I am now working with a test data set of only
10 records, it would not be at all accurate.
Maybe when I get the real test data set in 3 months - or so.

Thanks for all the help.

bill

#14

On 2010-07-05 12:09, bill wrote:
[...]

Quote:

select ... from episodes
where (ptnum, epiNum) in (
... <<<--------------doesn't this select require parens around it ?

No, and ...

Quote:

union
(select ...)

neither does this

Quote:

union
(select ...)

nor this

Quote:

)

[...]

Quote:

select ptNum, epiNum, ptStmtDir
from episodes e
join (

select ptNum, epiNum, postingDate as somedate
from transactions

union

select ptNum, epiNum, verifyDate as somedate
from paymentDistribution
join payment
on paymentDistribution.paymentDNumber = payment.paymentNumber

union

select ptNum, epiNum, date as somedate
from beginningBalances

) x
(e.ptnum, e.epiNum) = (x.ptnum, x.epiNum)
where somedate between cast('$firstdate' as date)
and cast('$lastdate' as date)

I can't do this as it retrieves one record per transaction instead of
one record per ptNum,epiNum pair.

I'm puzzled by your remark. This is logically equivalent with the
previous query. Is that query correct?

Quote:

Great question, but as I am now working with a test data set of only
10 records, it would not be at all accurate.
Maybe when I get the real test data set in 3 months - or so.

Can you post the table definitions (preferebly as create table
statements), and the sample data (also preferebly as insert statements),
and the expected result? I'm not sure whether we are in agreement of the
query design

/Lennart

[...]

#15

On Jul 5, 6:57*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:

Quote:

On 2010-07-05 12:09, bill wrote:
[...]

select ... from episodes
where (ptnum, epiNum) in (
* *... * * * <<<--------------doesn't this select require parens around it ?

No, and ...

* *union
* *(select ...)

neither does this

* *union
* *(select ...)

nor this

)

[...]

select ptNum, epiNum, ptStmtDir
from episodes e
join (

* * select ptNum, epiNum, postingDate as somedate
* * from transactions

* * union

* * select ptNum, epiNum, verifyDate as somedate
* * from paymentDistribution
* * join payment
* * * * on paymentDistribution.paymentDNumber = payment.paymentNumber

* * union

* * select ptNum, epiNum, date as somedate
* * from beginningBalances

) x
* * (e.ptnum, e.epiNum) = (x.ptnum, x.epiNum)
where somedate between cast('$firstdate' as date)
* * * * * * * * * *and cast('$lastdate' as date)

I can't do this as it retrieves one record per transaction instead of
one record per ptNum,epiNum pair.

I'm puzzled by your remark. This is logically equivalent with the
previous query. Is that query correct?

Great question, but as I am now working with a test data set of only
10 records, it would not be at all accurate.
Maybe when I get the real test data set in 3 months - or so.

Can you post the table definitions (preferebly as create table
statements), and the sample data (also preferebly as insert statements),
and the expected result? I'm not sure whether we are in agreement of the
query design

/Lennart

[...]

This would be very long and complex - let me play with it a bit first.
bill

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

subquery using IN with 2 values

comp.databases.mysql comp.databases.mysql

Re: subquery using IN with 2 values - 07-04-2010 , 12:30 PM

Re: subquery using IN with 2 values - 07-04-2010 , 12:32 PM

Re: subquery using IN with 2 values - 07-05-2010 , 05:09 AM

Re: subquery using IN with 2 values - 07-05-2010 , 05:57 AM

Re: subquery using IN with 2 values - 07-05-2010 , 07:58 AM