SQL query syntax with joins question

[SarahNZ]

With the join syntax used below is "k" a temporary table? What is 'k' and 'd'and how and where is it stored?


Code example:
select x.name, x.addressl1, x.city
into #temptable
from server.SQLServer.database.tablename x
join (
select name, addressl1, city, count(*) as instances
from (select distinct name, addressl1, city, code
from server.SQLServer.database.tablename) as d
group by name, addressl1, city
having count(*) > 1
) k on x.name = k.name, x.addressl1 = k.addressl1, x.city = k.city
join ( etc...

 

[Rhys666]

Sheesh...

'k' is the name of a table in the server and database this query is connected to, probably identifiable via the method of running this query, (i.e., the SqlConnection objec for ASP.net) - Where is it being run from?

'x' is a pseudoname for the table defined in the outermost query by 'server.SQLServer.database.tablename'

'#temptable' is a temporary table that will exist only as long as the connection thread this query is running through exists, it will be stored in tempDB on the server the query is connected to - Again, probably identifiable via the method of running this query.

'd' is a pseudoname for the correlated subquery '(select distinct name, addressl1, city, code from server.SQLServer.database.tablename)' - Again, probably identifiable via the method of running this query.

Rhys

 

[SQLSister]

Rhys, K is the name of a derived table defined by the query
select name, addressl1, city, count(*) as instances
from (select distinct name, addressl1, city, code
from server.SQLServer.database.tablename) as d
group by name, addressl1, city
having count(*) > 1

Sarah, when you want to join to a derived table you must give it a name which in this query is k.

x is an alias for the table referred to. Many people use them because it is shorter to refer to x.name than server.SQLServer.database.tablename.name

If you want to understand the structure of the query better, read in books online about derived tables, correlated subqueries and aliases.

 

[SarahNZ]

thanks!