Re: Problem with self join
| From: | Robin Vickery | Date: | Mon, 29 Nov 2004 12:50:12 +0000 |
| Subject: | Re: Problem with self join | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-203057@lists.php.net to get a copy of this message | ||
On Mon, 29 Nov 2004 14:12:23 +0530, suneel
<suneel@brainbayconsulting.com> wrote:
> Hi...
> Please take a look at the following...
>
> I'm using MySQL 4.0.15
> I have the database like this....
>
> Id name Owner
> 1 Top Menu 0
> 2 File 1
> 3 Open 1
> 4 Text File 2
> 5 Dot Net 2
> 6 ASP.Net 5
> 7 C#.Net 5
>
> In the above data the Id's 6 & 7 are not owner to any one.So,I
> want to retrieve those Id's which are not owner to any one.
This should really go to a MySQL list, but...
If your table is called "menus", this should do the job.
SELECT a.*
FROM menus a LEFT JOIN menus b ON a.Id = b.Owner
WHERE b.Owner IS NULL
-robin