[SOLVED] Using IN clause with subqueries. The compared column names are different

Using IN clause with subqueries. The compared column names are different

I have a table of elements and a table specifying the hierarchy between those elements. Knowing the alias of one element I want to get the aliases of its child elements. The columns specifying the "Element ID" parameter have different name in the two tables.

The element table has this (relevant) columns:

TYPE_EQUIP_CODE (The type of equipment)

ELEMENT_CODE (The identifier of the element)

ELEMENT_ALIAS (The alias of the element)

The hierarchy table has this (relevant) columns:

FATHER_ELEMENT_ID (The identifier of the father element)

FATHER_ELEMENT_TYPE (The type of equipment of the father element)

CHILD_ELEMENT_ID (The identifier of the child element)

ELEMENT_CODE and CHILD_ELEMENT_ID are the columns I want to compare

What I want is:

Knowing the Father alias and its type, get the aliases of its children.

Here is what I tried nesting subqueries:

SELECT ELEMENT_ALIAS
    FROM TUN_ELEMENT
        WHERE ELEMENT_CODE IN (
            SELECT * 
                FROM J_ELEMENT_HIERARCHY 
                    WHERE FATHER_ELEMENT_ID = (
                        SELECT ELEMENT_CODE 
                            FROM TUN_ELEMENT
                                WHERE ELEMENT_ALIAS = 'C-09-27-SAE-CR-002' AND TYPE_EQUIP_CODE = 4
                    ) 
                    AND FATHER_ELEMENT_TYPE = 4)

The subqueries work individually but running it all I get this error:

Solo se puede especificar una expresión en la lista de selección cuando la subconsulta no se especifica con EXISTS.. Error 116. SQLSTATE 42000. Severity 16. MsgState 1. Line 11.

Translated means:

Only one expression can be specified in the selection list when the subquery is not specified with EXISTS

What I'm trying to get is this, the aliases of the child elements:

ELEMENT_ALIAS

C-09-27-SAE-CR-002-D-11

C-09-27-SAE-CR-002-D-12

C-09-27-SAE-CR-002-D-13

Can this be done with subqueries or is it only doable with JOIN?

Solution

You must specify only 1 column to be returned in the subquery.
You can find more here.
Is it ELEMENT_ALIAS or FATHER_ELEMENT_ID that you want to compare with ELEMENT_CODE?

SELECT ELEMENT_ALIAS
    FROM TUN_ELEMENT
        WHERE ELEMENT_CODE IN (
            SELECT <Column name here> 
                FROM J_ELEMENT_HIERARCHY 
                    WHERE FATHER_ELEMENT_ID = (
                        SELECT ELEMENT_CODE 
                            FROM TUN_ELEMENT
                                WHERE ELEMENT_ALIAS = 'C-09-27-SAE-CR-002' AND TYPE_EQUIP_CODE = 4
                    ) 
                    AND FATHER_ELEMENT_TYPE = 4)