0

I'm wondering how to make an alias for a combination of double joined tables. So, the question is TableOne natural join TableTwo and then join TableThree with AID=AbID, and I'd like to give this combined table an alias like NewTable. I tried:

((TableOne NATURAL JOIN TableTwo) JOIN TableThree ON (AID = AbID) AS NewTable

but seems not working.

Zhiyuan Chen
  • 77
  • 8
  • Welcome to StackOverflow, please use the [edit](https://stackoverflow.com/posts/52790526/edit) link under your question to edit it, and provide the SQL code you are stuck in. – Ilyes Oct 13 '18 at 07:28
  • 1
    Please tag with the correct database that you are using. That query does not looks like a `SQL Server` query – Squirrel Oct 13 '18 at 07:40
  • SQL-Server does not have a `Natural Join` clause: https://stackoverflow.com/questions/4826613/natural-join-in-sql-server – Carsten Massmann Oct 13 '18 at 07:50
  • `natural join` is usually a good candidate for a join to avoid using. Later adding e.g. a CreatedDate to each table will silently stop your app working properly – Caius Jard Oct 13 '18 at 08:36

2 Answers2

0

You can (and should) give each table an alias when joining it:

 ((TableOne t1
   NATURAL JOIN TableTwo t2)
   JOIN TableThree t3
      ON (AID = AbID) AS NewTable

See also

SQL Table Alias

Corion
  • 3,855
  • 1
  • 17
  • 27
0

First, just don't use NATURAL JOIN. It is really broken, because it uses columns with the same name rather than properly declared foreign key relationships. It also makes the code really hard to debug. So use ON or USING instead.

Second, you can only do what you want with a subquery:

FROM . . . 
     (SELECT . . .               -- fill in with the columns you want
      FROM TableOne t1 JOIN
           TableTwo t2
           USING ( . . . ) JOIN  -- fill in with the columns used for the `JOIN`
           TableThree t3
           ON ?.AID = ?.AbID     -- fill in with the table aliases where the columns come from
     ) NewTable
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786