The outer join part of the "silly query" should return
(1) All pairs a,b for which (a.zone = b.zone ... and b.leftside != '')
(2) A row for each a that is not used in (1), with null for the columns
of b
The where clause then narrows these down to elements of (2) with a.type =
'MX'
No reason there shouldn't be any--and there are some. Your query does not
ask
for records with a.type='A' and a.type='MX', it asks for records of the left
join
with a.type='MX'.
In fact, for your "real" query, without the MX part, you should get a result
row
at least for each row of dns_rec for which leftside != ''. Sounds like you
should
get a lot of them, and it should take a while.
More generally,
select ... from L left join R on CONDITION
gives you:
(1) the result of the inner join,
select ... from L, R where CONDITION
(2) the rows from L that were not used in (1), with nulls for the R
columns
Then, you can apply an additional WHERE clause to that.
> Date: Wed, 22 Jan 2003 12:28:48 -0600 (CST)
> From: [EMAIL PROTECTED]
> To: [EMAIL PROTECTED]
> Subject: Left join returns records it shouldn't
> >Description:
>
> The following silly query (silly because it should return no
> records since it is impossible for a.type to have two different
> values simultaneously), returns two records when it should
> return none with the enclosed test data.
> select a.*, b.* from dns_rec a left join dns_rec b
> on (a.zone = b.zone and
> a.rightside = b.rightside and
> a.type = 'A' and b.type = 'A' and
> a.leftside = '' and b.leftside != '')
> where b.zone is null and a.type = 'MX'
> ;
> [...]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
