Ayuda con una consulta a dos tablas

[Thumper]

Hola haber si esto te sirve:

-- DECLARACION DE VARIABLES TIPO TABLA
DECLARE @TABLA_CATEGORIA TABLE ( INTIDCATEGORIA INT, INTIDCATEGORIAPADRE INT, VCHDESCRIPCION VARCHAR(100))
DECLARE @TABLA_PRODUCTO TABLE ( INTIDPRODUCTO INT, VCHDESCRIPCION VARCHAR(100), INTIDCATEGORIA INT)

-- LLENADO DE DATOS
INSERT INTO @TABLA_CATEGORIA VALUES( 1, 0, 'CATEGORIA 1')
INSERT INTO @TABLA_CATEGORIA VALUES( 2, 1, 'CATEGORIA 1.1')
INSERT INTO @TABLA_CATEGORIA VALUES( 3, 1, 'CATEGORIA 1.2')

INSERT INTO @TABLA_PRODUCTO VALUES( 1, 'PRODUCTO 1', 1)
INSERT INTO @TABLA_PRODUCTO VALUES( 2, 'PRODUCTO 2', 2)
INSERT INTO @TABLA_PRODUCTO VALUES( 3, 'PRODUCTO 3', 3)

-- VARIABLE IDCATEGORIA
DECLARE @IDCATEGORIA INT
SET @IDCATEGORIA = 1

-- SELECT QUE SOLO VA A DEVOLVER DATOS DE 2 NIVELES
-- DE LA CATEGORIA PADRE Y DE LOS HIJOS MAS NO DE LOS
-- NIETOS( SI LA CATEGORIA HIJO ES PADRE PARA OTRAS CATEGORIAS)
SELECT P.*
FROM @TABLA_PRODUCTO P
WHERE P.INTIDCATEGORIA = @IDCATEGORIA OR
P.INTIDCATEGORIA IN ( SELECT C.INTIDCATEGORIA
FROM @TABLA_CATEGORIA C
WHERE INTIDCATEGORIAPADRE = @IDCATEGORIA )