sql query getting information from one table for 2 fields

In this query ACCOUNTID and RESELLERID exist in dbo.CPM_OrbisAccounts  as ACCOUNTID
I can get the address information and account name for ,[sysdba].[OPPORTUNITY].[ACCOUNTID] but how do I do another query to get the [RESELLERID] into the output. basically I want to do two joins
inner join dbo.CPM_OrbisAccounts on dbo.CPM_OrbisAccounts.AccountID=[sysdba].[OPPORTUNITY].AccountiD
and
left outer join dbo.CPM_OrbisAccounts on dbo.CPM_OrbisAccounts.AccountID=[sysdba].[OPPORTUNITY].RESELLERID

not every opportunityid has a resellerid but every opportunityid has an accountid !

SELECT [sysdba].[OPPORTUNITY].[OPPORTUNITYID]
      ,[sysdba].[OPPORTUNITY].[ACCOUNTID]
      ,[RESELLERID]
      ,dbo.CPM_OrbisAccounts.*
       FROM [sysdba].[OPPORTUNITY]
         inner join dbo.CPM_OrbisAccounts on dbo.CPM_OrbisAccounts.AccountID=[sysdba].[OPPORTUNITY].AccountiD
Chris MichalczukConsultantAsked:
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.

Shaun KlineLead Software EngineerCommented:
Use a table alias:
SELECT [sysdba].[OPPORTUNITY].[OPPORTUNITYID]
      ,[sysdba].[OPPORTUNITY].[ACCOUNTID]
      ,Resellers.[RESELLERID]
      ,dbo.CPM_OrbisAccounts.*
       FROM [sysdba].[OPPORTUNITY]
         inner join dbo.CPM_OrbisAccounts on dbo.CPM_OrbisAccounts.AccountID=[sysdba].[OPPORTUNITY].AccountiD
         Left join dbo.CPM_OrbisAccounts Resellers on Resellers.AccountID = [sysdba].[OPPORTUNITY].ResellerID

Open in new window

Mark WillsTopic AdvisorCommented:
Concur.

When you have multiple output requirements from the same table source, you can effectively "name" that source by using a table alias (much the same way you can label a column by using the AS clause)

>>  not every opportunityid has a resellerid but every opportunityid has an accountid

Means the left join as you have already identified...

One small problem in Shaun's code is the table alias 'resellers' doesn't have the column resellerid, the column is accountid (correctly identified in the join, but not in the select).

When using an alias, it must be unique and identify the real origins. So, Shauns use of 'resellers' as a table alias is more appropriate than my use of 'R'.

But between the two, it should be more obvious for you as to how to use alias.

SELECT  O.[OPPORTUNITYID] AS OPPORTUNITY_ID
       ,A.[ACCOUNTID] AS ACCOUNT_ID
       ,R.[ACCOUNTID] AS RESELLER_ID
       ,R.*
       ,A.*
FROM   [sysdba].[OPPORTUNITY] AS O
inner join dbo.CPM_OrbisAccounts AS A on A.AccountID=O.AccountiD 
left outer join dbo.CPM_OrbisAccounts AS R on R.AccountID=O.RESELLERID

Open in new window

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
Mark WillsTopic AdvisorCommented:
Oh, and the only other thing to watch out for is duplicates (including exclusion of any rows). If there is an overlap in ID's in the CPM_OrbisAccounts table then you might need to further qualify those joins. Maybe something in Opportunity might indicate what "type", or, something in the accounts table can help identify what type of account it really is.

Hopefully the ID's are autogenerated and unique in the accounts table so no confusion between the ID's.

Using a Table Alias can create unwanted duplication. The other side of the argument is it can create is just the opposite effect and create unexpected exclusions... For example, the additional information required is really via additional columns where you might be wanting additional rows..

Sometimes you need to change around what you might instinctively think the joins should be.  In your example, it might well be the case that the real driver table is not Opportunity, but the Accounts table. It sounds like that is the real driver table. It has all the rows, where as opportunity has combined vales per row. Or, sometimes have to resort to a UNION query.

e.g.
SELECT  O.[OPPORTUNITYID] AS OPPORTUNITY_ID
       ,O.[ACCOUNTID] AS ACCOUNT_ID
       ,A.*
FROM   OPPORTUNITY AS O
inner join CPM_OrbisAccounts AS A on A.AccountID=O.AccountiD 

UNION

SELECT  O.[OPPORTUNITYID] AS OPPORTUNITY_ID
       ,O.[ACCOUNTID] AS ACCOUNT_ID
       ,R.*
FROM   OPPORTUNITY AS O
inner join CPM_OrbisAccounts AS R on R.AccountID=O.RESELLERID

Open in new window


So, just using a Table Alias might not always be the answer you need, but it helps differentiate a common source used multiple times.

If you want, I can work up some examples for you...
Vitor MontalvãoMSSQL Senior EngineerCommented:
chrismichalczuk, do you still need help with this question?
Chris MichalczukConsultantAuthor Commented:
this worked thanks
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.