как-то так

А пустые неудаленные показываем, верно?

select j.*
from job j
where j.delete = 0 or j.jobid in
	(select j1.jobid
	from job j1
		inner join job j2
		on j2.parent_jobid = j1.jobid)

Не знаю mysql, поэтому в запросе могут быть синтаксические ошибки. И насчет оптимальности не уверен, х.з. как там в mysql оптимизатор работает. Можно также через exists вместо in написать, можно попробовать обойтись одним join без вложенного запроса.

Кстати, а не надо ли удалять вложенные узлы при удалении родительского?

А пустые неудаленные показываем, верно?

да

Кстати, а не надо ли удалять вложенные узлы при удалении родительского?

Нет. Я задачу несколько упростил. Выделили именно то счем у меня проблема.

Спасибо за наводку. С синтаксисом разберусь.

Лучше было использовать nested sets.

Да слышал, там выборки подобного рода проще делаются.

А если признак удаленности хранить в отдельной таблице

mysql> select * from status limit 0,1;
+-------+----------+
| jobid | delete   |
+-------+----------+
|     1 |        1 |
+-------+----------+
|     2 |        0 |
+-------+----------+
1 row in set (0.00 sec)

select j.*
from job j INNER JOIN status USING ( jobid ), 
where j.delete = 0 or j.jobid in
	(select j1.jobid
	from job j1
		inner join job j2
		on j2.parent_jobid = j1.jobid)

Может нужно в вашем варианте заменить проверку j.delete = 0 на что-то вроде s.delete = 0 и добавить алиас s для status. Иначе вообще ваш запрос подозрительно выглядит, т.к. вы джойните с таблицей status, но фактически не используете значения из неё. Так и задумано?

Так и задумано?

Я SQL плохо знаю.

Может нужно в вашем варианте заменить проверку j.delete = 0 на что-то вроде s.delete = 0 и добавить алиас s для status.

Если времени не жалко. Черкните примерчик.

пожалуйста

select j.*
from job j INNER JOIN status s USING ( jobid ), 
where s.delete = 0 or j.jobid in
	(select j1.jobid
	from job j1
		inner join job j2
		on j2.parent_jobid = j1.jobid)

Спасибо, работает. Но если в ветке все узлы удалены, то корневой узел запрос отображает.