At 2:31 PM +0100 10-03-2000, Mark Hodnett wrote:
>Ok, I'll have a shot at this! This might not be exactly correct, as
>I didn't try it out, but try to follow the logic and adapt it for
>your query.
>
>SELECT t1.name
>FROM table1 t1 LEFT JOIN table2 t2 ON t1.aid = t2.aid, table3 t3
>WHERE (t2.bid = t3.bid AND t3.status = 5) AND t2.aid IS NULL
>
>To do a NOT IN sub-query using JOIN's, use a LEFT JOIN and check
>that the second table has a NULL value for the field used in the
>JOIN. So, here we have "table1 t1 LEFT JOIN table2 ON t1.aid =
>t2.aid.....AND t2.aid IS NULL". This will select all the records in
>t1, where aid is not in t2.
>
>Regards
>
>Mark
>
>P.S. When people suggest the manual, they are usually trying to be
>helpful and the answer is contained within....
Yep.
What I'm curious about is the assertion (below) that the tutorial says
that ALL subqueries can be converted to LEFT JOIN. I know of one place
in the manual that says *some* of them can. Where does it say they all
can?
>
>At 14:39 03/10/00 +0200, you wrote:
>>Hello,
>>I´m sorry that my latest mail has been HTML formatted.
>>
>>Regarding mail about subquerys to join statements.
>>I have allready read the tutorial about converting subquertys to left join.
>>The tutorial says that ALL subquerys can be changd to left join
>>statements and i was wondering how this is made in this specific
>>case.
>>
>>SELECT t1.name
>>FROM table1 t1
>>WHERE t1.aid NOT IN (
>> SELECT t2.aid
>> FROM table2 t2, table3 t3
>> WHERE t2.bid = t3.bid AND t3.status = 5
>> );
>>
>>Table1 consists of two columns, aid and name
>>Table2 consists of two columns, aid and bid
>>Table3 consists of two columns, bid and status
>>
>>It from an old system made for MSSQLServer 6.5 and now i´m trying
>>to convert it into MySQL
>>
>>how can i make this statement into a left join. Please make better
>>answer this time than:
>>"read the tutorial" or "subquerys don´t work in MySQL"
>>
>>Best Regards
>>Micke
>>
>>
>>--
>>---------------------------------------------------------------------
>>Please check "http://www.mysql.com/documentation/manual.php" before
>>posting. To request this thread, e-mail mysql-thread52283@stripped
>>
>>To unsubscribe, send a message to:
>> <mysql-unsubscribe-mark.hodnett=hammerauction.com@stripped>
>>
>>If you have a broken mail client that cannot send a message to
>>the above address (Microsoft Outlook), you can use:
>> http://lists.mysql.com/php/unsubscribe.php
>
>
>--
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/documentation/manual.php" before
>posting. To request this thread, e-mail mysql-thread52291@stripped
>
>To unsubscribe, send a message to:
> <mysql-unsubscribe-paul=snake.net@stripped>
>
>If you have a broken mail client that cannot send a message to
>the above address (Microsoft Outlook), you can use:
> http://lists.mysql.com/php/unsubscribe.php
--
Paul DuBois, paul@stripped
