Conditional joining

I'm working on some nested views to consolidate them into one view. in the consolidated view I have an opportunities table and a quotes table that I'm doing a full join on opportunity id so that I get all quotes, all opportunities, and the ones that are associated.

Now, the interesting bit is that I've got a table called accounts that I need to join to. seeing as not all opportunities have quotes and vice versa, I need a good way to join to the table to get the account no matter what (and, in theory, if there is an opportunity AND a quote they SHOULD have the same account). for consistency if both exist I want to use the data from the quote.

The first thing I tried was this:

left outer join dbo.Accounts a
on q.AccountID=a.AccountID
or o.AccountID=a.AccountID

Now the problem I have with that is that if I have both a quote AND开发者_开发百科 opportunity it seems that it will return multiple rows, and that's not what I want.

The second try I came up with this:

left outer join dbo.Accounts a
on coalesce(q.AccountID,o.AccountID)=a.AccountID

this seems to be giving me the accurate results, but I'm curious if there's a better way to get what I want without resulting to a function in the join.

It seems to me that if you always have an account number you are working this backwards. You should be starting at the Acoount table and left joining to the others.

SELECT a.accountid, OQ.somefield, OQ.someotherfield
FROM   
    (Select coalesce (o.AccountID, q.AccountID) as AccountID,
            q.somefield, o.someotherfield
    FROM Opportunity o 
    FULL JOIN Quote q 
        ON q.opportunityId = a.opportunityId) OQ
LEFT JOIN Accounts A
    ON OQ.AccountID = A.AccountId

Now granted this will give you multiple rows if you have mutiple Opportunities or Quotes for the same account. And you might need some other factors in the left join to relate Opportunities to Quotes. I don't know your data to be sure.

Good for you that you are replacing the nastiness that is the nested view.

Use the keyword DISTINCT with your query. It returns only unique entries. So your accounts should only appear once.