Charles Kline <[EMAIL PROTECTED]> wrote on 07/06/2005 02:25:12 PM:
> Hi all,
>
> I have a table:
>
> id, name, parent_id
>
> I need to find all records that have no children. I know how to do it
> using a sub select, but I need to do this in version MySQL 3.32 and I
> am not sure how.
>
> Thanks,
> Charles
>
>
> --
> RightCode, Inc.
> 900 Briggs Road #130
> Mount Laurel, NJ 08054
> P: 856.608.7908
> F: 856.439.0154
> E: [EMAIL PROTECTED]
>
>
Use a temp table....
CREATE TEMPORARY TABLE tmpParents
SELECT DISTINCT parent_id FROM tablename;
SELECT t.id, t.name
FROM tablename t
LEFT JOIN tmpParents tp
ON tp.parent_id = t.id
WHERE tp.parent_id is null;
DROP TEMPORARY TABLE tmpParents;
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
