SQL logic

display only when no children

[ukgimp]

Hello

There is a table containing a list of names and a parent column which is set to NULL if a top level. If there is a parent it give the parentID. Is it possible to get only names that have no chldren (eg bottom levle). I cannot for the life of me think the logic through. Perhaps this concept has a name to help me track it down?

TIA

Cheers

[Dreamquick]

SELECT *
FROM MyTable
WHERE ID IN(
~~SELECT DISTINCT A.ID
~~FROM MyTable AS A
~~~~LEFT JOIN MyTable AS B ON A.ID = B.ParentID AND B.ParentID IS NOT NULL
~~WHERE A.ID IS NOT NULL AND B.ParentID IS NULL
);

That should join the two tables together on the ID = ParentID

If the ParentID is null as a result of this join then that means the original record's ID was never used as a ParentID (I hope)

If an ID for a record was never used as a parent ID, that record never had any children.

Once we know which records are child-less then we can just retrieve them.

<added after a quick test>
Hey that stuff might actually work!

I tested it with this data;

ID~~~~~~Name~~~~~~~~~~~~ParentID
1~~~~~~~Person A~~~~~~~~NULL
2~~~~~~~Person B (A)~~~~1
3~~~~~~~Person C (AB)~~~2
4~~~~~~~Person E~~~~~~~~NULL

...and that query returned "Person C" & "Person E"

</added>
- Tony

[ukgimp]

Thanks Tony

That is a big help. It will take me little time because of course i am using mySQL and that does not like subselects etc. I think i need to read up on joins.

Cheers

[andreasfriedrich]

Does MySQL handle sub selects by now?

[Dreamquick]

Sub-select?

That's just your bog-standard IN() statement isn't it?

Hmm I guess I take far too many things for granted...

- Tony

[ukgimp]

I managed to split the array and print out a variable then ram that into the IN() doo dar once before. So it looks like I will have to try that method again.

[andreasfriedrich]

MySQL Server currently only supports nested queries of the form INSERT ... SELECT ... and REPLACE ... SELECT .... You can, however, use the function IN() in other contexts.

Meanwhile, you can often rewrite the query without a subselect:

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

This can be rewritten as:

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;

[mysql.com...]

[Dreamquick]

If it helps you might be able to use this;

SELECT DISTINCT A.*
FROM MyTable AS A
~~LEFT JOIN MyTable AS B ON A.ID = B.ParentID AND B.ParentID IS NOT NULL
WHERE A.ID IS NOT NULL AND B.ParentID IS NULL

I was just being lazy using an IN() statement as I felt it was a little better than forcing a distinct.

- Tony

[ukgimp]

Thanks Tony and Andreas

It appears that I am moving in the right direction now. I have been to that section of the mySQL site regarding rewriting the query before and struggled.

[mysql.com...]

I dont mind saying, it baffles me. I cannot even see a pattern. Normally I can figure things like that out, but this has me stumped

?