sethu sethu - 3 months ago 7
SQL Question

Is this a valid query?

I am trying to create nested join statements and use alias for programming purposes. Let me give you a list of tables I'm trying to join and their foreign keys.

TABLE1: id (Foreign key)
TABLE2: id (primary key), idx (foreign key)
TABLE3: id (primary key), idx (primary key)
TABLE4: id (primary key), idx (primary key)`

SELECT *
FROM (((TABLE1 JOIN
TABLE2
ON TABLE1.id = TABLE2.id
) AS NEW_TABLE JOIN
TABLE3
ON NEW_TABLE.id = TABLE3.id AND NEW_TABLE.idx = TABLE3.idx
) AS NEW_TABLE JOIN
TABLE4
ON NEW_TABLE.id = TABLE4.id AND
NEW_TABLE.idx = TABLE4.idx
);


Can you let me know if this is a valid query and using aliases this way works?

Answer

I don't think so. I would write this as:

SELECT * 
FROM TABLE1 JOIN
     TABLE2
     ON TABLE1.id = TABLE2.id JOIN
     TABLE3
     ON TABLE1.id = TABLE3.id AND TABLE2.idx = TABLE3.idx
     TABLE4
     ON TABLE1.id = TABLE4.id AND
        TABLE2.idx = TABLE4.idx;

(Well, actually, I would use table aliases but this is the idea.)

The parentheses do not do anything for you. I also think re-using the NEW_TABLE alias is likely to generate an error.