sql Help

I am running the query and getting an error message .

The column 'userKey' was specified multiple times for 'T2'.


SELECT * 

FROM (
	SELECT * 
	FROM trans T
	inner join users U on U.userKey = T.userKey
	LEFT OUTER JOIN (
		orders O 
		inner join lineitems LI on LI.orderKey = O.orderKey
	)on  T.userKey = O.userKey
	WHERE O.void = 'N'
	AND T.TransDate BETWEEN '07/17/2015'
                            AND '07/18/2015'
)T2

Open in new window

LVL 19
erikTsomikSystem Architect, CF programmer Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
I assume the outer SELECT is just a simplification for posting here,  otherwise it just doesn't make any sense and can be left out. It is also the culprit for enforcing unique column (Result)  names.
There are duplicate column names in the inner SELECT. Usually that is no issue,  but if used as an ad-hoc view (inline table,  ...), all resulting columns need to be unqiue. Since you provided no aliases, the common columns of both tables are there twice.
You'll have to enumerate the columns you want to get, and make sure none of them are duplicates.
Jim HornMicrosoft SQL Server Data DudeCommented:
Agreed.  SELECT * from A WOMPLOAD OF TABLES is a poor programming practice, as it means that if those tables have columns of the same name then there will be issues returning that set.  Usually this will happen with the JOIN keys.

And if that select is a subquery, it could throw the error.
This would also cause SSRS / SSIS to throw an error as well if it were the source of data.

So...
Do you reeeeeallllyyy need to SELECT * from all tables, or will a subset of columns work?
Guessing if anything you should spell out the columns, intentionally not duplicate columns of the same name but from different tables.
dameyCommented:
Your SQL should look something like this, replace COL1... with actual columns you need for your results and alias any columns that are named the same in multiple tables...

/* Need to be explicit for your columns, you can't ask for orderKey to display from multiple tables to be in the results or any column that is named the same in multiple tables without alaising them
Only return what you need to display and if you want to display all the joined Keys you must alias them
Your query should look something like...
 
*/

      SELECT T.orderKey, T.COL1, T.COL2, T.COL3, Items.Col1, Items.Col2 Items.Col3
      FROM trans T
      inner join users U on U.userKey = T.userKey
      LEFT OUTER JOIN
      /*
          Use subselect and alias it
        This could be written differently but I'm going with the sql logic you provided
        You'll need to use ordeyKey in your sub select but only from the orders table
        Explicitly select the columns required from your order & lineItems table
        */
         
      (SELECT O.orderKey, O.void, O.COL2, O.COL3, LI.COL1, LI.COL3
      FROM orders O
      INNER JOIN lineitems LI on LI.orderKey = O.orderKey
      ) [Items] on  T.userKey = Items.userKey
      WHERE Items.void = 'N'
      AND T.TransDate BETWEEN '07/17/2015'
                            AND '07/18/2015'

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
in addition don't use BETWEEN for date range filters

plus, believe it or not MM/DD/YYYY is not a universally understood date format. The safest possible format for date literals in SQL Server is: YYYYMMDD

 WHERE Items.void = 'N'
     AND ( T.TransDate >= '20150717' AND T.TransDate < '20150718' )

This date filter will give you exactly 1 day of transactions.

See: "Beware of Between"
awking00Information Technology SpecialistCommented:
Don't know what error you're getting, but try this -
SELECT * FROM
(SELECT * FROM trans T inner join users U on U.userKey = T.userKey
 LEFT OUTER JOIN
 (SELECT O.userKey, o.void FROM orders O inner join lineitems LI
  on LI.orderKey = O.orderKey
  where .Void = "N'
  ) X
  ON T.userKey = X.userKey
  WHERE T.TransDate BETWEEN '07/17/2015' AND '07/18/2015'
) T2
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.