Search code examples
sql-servert-sqlansi-sql

T-SQL: Query brings back too many columns

I've run across a query that seems to be bringing back too many columns (yes, columns, not rows). The statement is below

Select TOP 10 PERCENT
       TempLegs.*,
       TMWStateMilesByLeg.*
From
(    
   Select  TOP 10
      TempOrigin.stp_city as 'Origin Location'
        from  stops TempOrigin (NOLOCK)
   Union
   Select  TOP 10
      TempOrigin2.stp_city as 'Origin Location'
        from  stops TempOrigin2 (NOLOCK)
) As TempLegs,TMWStateMilesByLeg

When I run this statement, my result set isn't a single column of 'Origin Location', but instead lists all the columns from the stops table. What's going on here?

The only thing that I can think of is that the

As TempLegs,TMWStateMilesByLeg

statement is aliasing both pieces of the join, but I'm not sure as I've never see two terms in an AS statement.

Any help is greatly appreciated.

Solution

  • that is an old style join.

    Select TOP 10 PERCENT /* <-- top without order by */
    TempLegs.*
       --,TMWStateMilesByLeg.* 
From (    
   Select  TOP 10 /* <-- top without order by */
      TempOrigin.stp_city as 'Origin Location'
        from  stops TempOrigin --(NOLOCK)
   Union /* <-- union instead of union all will return distinct results */
   Select  TOP 10 /* <-- top without order by */
      TempOrigin2.stp_city as 'Origin Location'
        from  stops TempOrigin2 --(NOLOCK)
  ) As TempLegs --,TMWStateMilesByLeg

    Reference: