By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,310 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,310 IT Pros & Developers. It's quick & easy.

Subquery in Join Statement

P: n/a
I have the following query I wrote in MySQL 5.0

Select
eq_employees.empid,
eq_sigreturns.returnid
From
eq_employees
Left Join eq_sigreturns ON eq_employees.empid
=
eq_sigreturns.empid
in (
Select eq_sigreturns.returnid,
eq_sigreturns.empid
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

Mar 1 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
So I made the following change

Select
eq_employees.empid,
eq_sigreturns.returnid in
(
Select eq_sigreturns.returnid
From
eq_sigreturns
where
eq_sigreturns.`month` = '12' AND
eq_sigreturns.`year` = '2005'
)
From
eq_employees
Left Join eq_sigreturns ON eq_employees.empid = eq_sigreturns.empid

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.

Mar 1 '06 #2

P: n/a
"kevinjbowman" <ke**********@gmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...
... From eq_employees
Left Join eq_sigreturns ON eq_employees.empid = eq_sigreturns.empid
in (
Select eq_sigreturns.returnid,
eq_sigreturns.empid ...


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.empid = eq_sigreturns.empid
AND eq_sigreturns.empid 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.empid = eq_sigreturns.empid
AND eq_sigreturns.`month` = 12 AND eq_sigreturns.`year` = 2005

Regards,
Bill K.
Mar 1 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.