John K N
### Question
Is it possible to query a normalised table twice in a statement without using an alias?
### Test Data
Taken from [this db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=efd9630aacb3ebbe440bda1893a2bcf2)
> create table servers
> (
> srv_id int identity(1,1),
> srv_name nvarchar(20),
> srv_state_id int
> )
> ;
> GO
> insert into servers
> (
> srv_name, srv_state_id
> )
> values
> ('server1', 1),
> ('server2', 2)
> ;
> GO
> create table instances
> (
> ins_id int identity (1,1),
> ins_name nvarchar(20),
> srv_id int,
> ins_state_id int
> )
> ;
> GO
> insert into instances
> (ins_name, srv_id, ins_state_id)
> values
> ('general', 1, 1),
> ('newinstance', 2, 2),
> ('special', 1, 2 ),
> ('trouble', 2, 1)
> ;
> GO
> create table databases
> (
> dbs_id int identity(1,1),
> dbs_name nvarchar(20),
> dbs_ins_id int,
> dbs_state_id int
> )
> ;
> GO
>
> insert into databases
> (dbs_name, dbs_ins_id, dbs_state_id)
> values
> ('master', 1, 1),
> ('master', 2, 2),
> ('master', 3, 3),
> ('master', 4, 1),
> ('msdb', 1, 1)
> ;
> GO
> create table states
> (
> state_id int,
> state_name nvarchar(20),
> state_state_id int
> )
> ;
> GO
>
> insert into states
> (state_id, state_name, state_state_id)
> values
> (1,'Prod', 1),
> (2, 'Test', 1),
> (3, 'Dev', 1),
> (4, 'Res', 2)
> ;
> GO
>
> select * from servers;
> GO
>
> srv_id | srv_name | srv_state_id
> -----: | :------- | -----------:
> 1 | server1 | 1
> 2 | server2 | 2
> select * from instances;
> GO
>
> ins_id | ins_name | srv_id | ins_state_id
> -----: | :---------- | -----: | -----------:
> 1 | general | 1 | 1
> 2 | newinstance | 2 | 2
> 3 | special | 1 | 2
> 4 | trouble | 2 | 1
> select * from databases;
> GO
>
> dbs_id | dbs_name | dbs_ins_id | dbs_state_id
> -----: | :------- | ---------: | -----------:
> 1 | master | 1 | 1
> 2 | master | 2 | 2
> 3 | master | 3 | 3
> 4 | master | 4 | 1
> 5 | msdb | 1 | 1
> select * from states;
> GO
> state_id | state_name | state_state_id
> -------: | :--------- | -------------:
> 1 | Prod | 1
> 2 | Test | 1
> 3 | Dev | 1
> 4 | Res | 2
### Standard Query To Determine States of Server, Instances, Databases
A standard query to determine the states of my servers, instances and databases would look something like this:
> select
> srv_name,
> srvsta.state_name,
> ins_name,
> inssta.state_name,
> dbs_name,
> dbssta.state_name
> from servers as srv
> join states as srvsta
> on srv.srv_state_id = srvsta.state_id
> join instances as ins
> on ins.srv_id = srv.srv_id
> join states as inssta
> on ins.ins_state_id = inssta.state_id
> join databases as dbs
> on dbs.dbs_ins_id = ins.ins_id
> join states as dbssta
> on dbs.dbs_state_id = dbssta.state_id
> ;
> GO
>
> srv_name | state_name | ins_name | state_name | dbs_name | state_name
> :------- | :--------- | :---------- | :--------- | :------- | :---------
> server1 | Prod | general | Prod | master | Prod
> server2 | Test | newinstance | Test | master | Test
> server1 | Prod | special | Test | master | Dev
> server2 | Test | trouble | Prod | master | Prod
> server1 | Prod | general | Prod | msdb | Prod
*db<>fiddle [here](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=efd9630aacb3ebbe440bda1893a2bcf2)*
### Question
Is it possible to query a normalised table twice or three times in a single statement without using an alias?