sethu sethu - 3 months ago 7
SQL Question

Is this a valid query? (Repetition of Aliases)

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

you probably need to nest complete queries:

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

although, without knowing the contents of the tables it's hard to say what kind of results you might get or whether your query is even valid.

Comments