Subquery in Join Statement

[kevinjbowman]

I have the following query I wrote in MySQL 5.0

Select
eq_employees.em pid,
eq_sigreturns.r eturnid
From
eq_employees
Left Join eq_sigreturns ON eq_employees.em pid
=
eq_sigreturns.e mpid
in (
Select eq_sigreturns.r eturnid,
eq_sigreturns.e mpid
From
eq_sigreturns
where
eq_sigreturns.` month` = '12' AND
eq_sigreturns.` year` = '2005'
)

To return all records from the employees table and the returnid from
the sigreturns table where the month and year match.

The query throws an error message, I assume meaning I can't have a
subquery in my join statement. However anywhere else I put the
subquery seems to negate the left join and I only get empids with
matching records in the sigreturns table

Thanks,
Kevin

[kevinjbowman]

So I made the following change

Select
eq_employees.em pid,
eq_sigreturns.r eturnid in
(
Select eq_sigreturns.r eturnid
From
eq_sigreturns
where
eq_sigreturns.` month` = '12' AND
eq_sigreturns.` year` = '2005'
)
From
eq_employees
Left Join eq_sigreturns ON eq_employees.em pid = eq_sigreturns.e mpid

I get all records from the eq_emloyees database but I get a 0 instead
of the returnid for records that meet the query requirements.

[Bill Karwin]

"kevinjbowm an" <ke**********@g mail.com> wrote in message
news:11******** **************@ v46g2000cwv.goo glegroups.com.. .

... From eq_employees
Left Join eq_sigreturns ON eq_employees.em pid = eq_sigreturns.e mpid
in (
Select eq_sigreturns.r eturnid,
eq_sigreturns.e mpid ...

1. You can only do one predicate at a time. If you do an expression like
a = b IN (1,2,3)
The order of evaluation is unclear. Does it test b IN (1,2,3) and return
0 or 1, then compare that 0 or 1 to a? Or does it compare a = b and return
0 or 1, then compare that to (1,2,3)? Depends on the order of evaluation
between = and IN. But this expression definitely does not imply the logic
of a = b AND b IN (1,2,3).

2. When using IN (SELECT ...), put only one column in the select-list of the
subquery, the column which you compare to the expression on the
left-hand-side of the IN.

This could work:

SELECT ...
FROM eq_employees LEFT JOIN eq_sigreturns
ON eq_employees.em pid = eq_sigreturns.e mpid
AND eq_sigreturns.e mpid IN ( SELECT empid FROM eq_sigreturns WHERE
`month` = 12 AND `year` = 2005)

In this case, this is equivalent to the following simplified form:

SELECT ...
FROM eq_employees LEFT JOIN eq_sigreturns
ON eq_employees.em pid = eq_sigreturns.e mpid
AND eq_sigreturns.` month` = 12 AND eq_sigreturns.` year` = 2005

Regards,
Bill K.