PHP SQL - ..WHERE t1.a IN( t1.b );

Author

..WHERE t1.a IN( t1.b );

barryke

2004-04-22, 8:34 am

Definition:
I have a table t1, with collumns id, id_parent, and name.
This t1.id_parent could be NULL or contain some other t1.id.
Purpose is to make a expandable and collapsable treeview.
Now it could be handled by using subquery's, but the server
doesn't allow to use these. Older mysql version. Thats a fact, i
can not change it. :(

[b]Problem:[/b]
Actualy there are lots of left join's etc in the real query,
it's huge.
Simply put, left out all irrelevant things, it looks like
this.--------
SELECT t1a.id, t1a.id_parent, t1a.name
FROM t1 AS t1a, t1 AS t1b
WHERE NOT ( t1a.id_parent IN(t1b.id) )
OR t1.id_parent IS NULL;
--------
Old query was: --------
SELECT id, id_parent, name
FROM t1
--------

[b]Question:[/b]
I just want to have all [b]id[/b]'s
of the records from [b]t1[/b]
having
no [b]id_parent[/b] pointing to them,
[u]or[/u] that who are [b]NULL[/b].

I dont see it. :blink: But looked everywhere.
though http://dev.mysql.com/doc/mysql/en/R...subqueries.html has
some info about this. Also see second-to-last post.

:) live & love life 2b happy !!

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

barryke

2004-04-29, 11:10 am

Solved.

This way:

--------
SELECT
relatie.id AS 'id',
relatie.name AS 'name',
FROM relatie
LEFT JOIN relatie AS `relatie_parent` ON
relatie.id_parent = relatie_parent.id
WHERE
( relatie_parent.id IS NULL
OR relatie.id_parent IS NULL
)
--------

if the LEFT JOIN cant join some relation's id_parent to an other
relation's id, it will make this id NULL. So i joint them and
only showed the blanc

:) live & love life 2b happy !!

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

Sponsored Links