Где ошибка в SQL запросе?

Question

[Nordman99]

есть база данных, в ней две таблицы TABLE_IN_OUT и TABLE_REFERENCE, таблица TABLE_IN_OUT - список прихода/выбытия некоторых материалов
Ней дата поступления(выбытия) материала и код материала из таблицы TABLE_REFERENCE

Таблица TABLE_IN_OUT вида:

Date	IN                            OUT                                                          ID
Дата    (Данные о поступлении)	    (данные о выбытии)	      Код материала из справочника TABLE_REFERENCE(В TABLE_REFERENCE это тоже столбец ID)

Таблица TABLE_REFERENCE со справочными данными (Коды материалов вида (по столбцам):

ID			              ID_PARENT			      DESCRIPTION
(Сам код материала)    (Отношение данного 
			              материала к выстостоящей
		                      категории материалов)
1			            0				   Наименование1
2			            1				   Наименование2
3			            2				   Наименование3

Т.е. в столбце ID таблицы TABLE_IN_OUT указан код материала, допустим 3, но что это за материал неизвестно, для этого надо заглянуть в TABLE_REFERENCE столбец ID - 3 это будет "Наименование3", но посмотрев в столбец ID_PARENT также можно узнать что данный материал "Наименование3" относится к вышестоящей категории 2 а это будет "Наименование2", количество вложенностей, т.е. отношений к вышестоящей категории может быть неограниченным, я хочу создать виртуальную таблицу TABLE_VIRTUAL
чтобы получить таблицу где можно будет видеть наглядно наименование материала и хотябы две вышестоящих категорий к которой он относится, итого таблица должна выглядеть примерно так:

Date	IN        OUT	Наименование 	Категория1	   Категория2

Дата    ...      ...	Наменование1   Наименование2      Наименование3

создал запрос:

CREATE VIEW TABLE_VIRTUAL AS 
SELECT TABLE_IN_OUT.Date AS "Date", TABLE_IN_OUT.IN AS "IN", TABLE_IN_OUT.OUT as "OUT", 
	(SELECT DESCRIPTION FROM TABLE_REFERENCE WHERE TABLE_IN_OUT.ID=TABLE_REFERENCE.ID) as "Наименование", 
	(SELECT DESCRIPTION FROM (SELECT * FROM TABLE_REFERENCE WHERE TABLE_REFERENCE.ID = (SELECT TABLE_REFERENCE.ID_PARENT FROM TABLE_REFERENCE WHERE TABLE_REFERENCE.ID=TABLE_IN_OUT.ID))) as "Категория1",
	(SELECT DESCRIPTION FROM (SELECT * FROM TABLE_REFERENCE WHERE TABLE_REFERENCE.ID = (SELECT TABLE_REFERENCE.ID_PARENT FROM (SELECT * FROM TABLE_REFERENCE WHERE TABLE_REFERENCE.ID = (SELECT TABLE_REFERENCE.ID_PARENT FROM TABLE_REFERENCE WHERE TABLE_REFERENCE.ID=TABLE_IN_OUT.ID))))) as "Категория2"

FROM TABLE_IN_OUT
	INNER JOIN TABLE_REFERENCE
		ON TABLE_IN_OUT.ID = TABLE_REFERENCE.ID

В итоге в виртуальной таблице TABLE_VIRTUAL столбцы Date, IN, OUT, Наименование и Категория1 заполняются правильно но столбец Категория2 все время NULL, вроде запрос SELECT для этого столбца я сформировал правильно

(SELECT DESCRIPTION FROM (SELECT * FROM TABLE_REFERENCE WHERE TABLE_REFERENCE.ID = (SELECT TABLE_REFERENCE.ID_PARENT FROM (SELECT * FROM TABLE_REFERENCE WHERE TABLE_REFERENCE.ID = (SELECT TABLE_REFERENCE.ID_PARENT FROM TABLE_REFERENCE WHERE TABLE_REFERENCE.ID=TABLE_IN_OUT.ID)))))  as "Категория2"

Почему NULL?

Comments

[mletov]

Вряд ли кто-то будет разбирать ваш запрос целиком.

Декомпозируйте.

Попробуйте написать SELECT только для одной строчки, подставьте конкретные айдишки и посмотрите что он выводит.
Постепенно сужайте область поиска, пока не найдете причину.

Answer 1

[Rsa97]

SELECT io.Date, io.IN, io.OUT, m.DESCRIPTION name,
       p1.DESCRIPTION parent, p2.DESCRIPTION grandparent
  FROM TABLE_IN_OUT io
  JOIN TABLE_REFERENCE m ON m.ID = io.ID
  LEFT JOIN TABLE_REFERENCE p1 ON p1.ID = m.ID_PARENT
  LEFT JOIN TABLE_REFERENCE p2 ON p2.ID = p1.ID_PARENT

Comments

[Nordman99]

Спасибо большое!

Answer 2

[Adamos]

Если эта выборка нужна постоянно - удобнее будет завести текстовое поле в таблице TABLE_REFERENCE, в котором при добавлении каждой строки записывается аналогичное поле родительской категории плюс разделитель плюс название (или ID, или и то и другое - смотря что вам с этими данными нужно делать) этой родительской категории. Так у каждой строчки будет в ее данных все вышестоящее дерево категорий, без подзапросов.
Особенно если вложенность непредсказуема.