List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 3 2000 1:50pm
Subject:Re: left join from subquerys
View as plain text  
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
Thread
left join from subquerysMicke Bjork Coolbase3 Oct
  • Re: left join from subquerysMark Hodnett3 Oct
    • SV: left join from subquerysMicke Bjork Coolbase3 Oct
    • Re: left join from subquerysPaul DuBois3 Oct