How Do I Force Data From First Table to Display

DB: SQL SEVER 2008

I'm joining multiple tables and I want the results to always show the data from the "Project" table. In the example below, I have a client where billing and discount data is in separate tables and don't have much in common except the project id. I have been able to get close to what I want, but I'm having trouble getting the data from the first table to display in the resulting rows. Currently, we get this data from multiple queries, but we're trying to combine it into one.

I tried changing the joins and union. I was also trying to avoid using case statements if possible.

Sample Data on
SQL Fiddle: http://sqlfiddle.com/#!18/ec1e7/8

Query:

select * from project
    left join billable
        on project.pproject = billable.bproject
   full join discount 
        on billable.bindex = discount.dindex;

Result:

```
| pproject | pcontact | pdesc | pclient | bindex | bproject | bbillhrs | bbillrt | bbillamt | binvoice | bbatch | dindex | dproject | drate | damount | dinvoice | dbatch |
|----------|-------------|------------------------------|---------|--------|----------|----------|---------|----------|----------|--------|---------|----------|--------|---------|----------|--------|
| 10-70005 | bill murray | this is the best description | 10 | 7319 | 10-70005 | 0.2 | 40 | 8 | 182 | 5704 | (null) | (null) | (null) | (null) | (null) | (null) |
| 10-70005 | bill murray | this is the best description | 10 | 7681 | 10-70005 | 1.1 | 50 | 55 | 182 | 7552 | (null) | (null) | (null) | (null) | (null) | (null) |
| 10-70005 | bill murray | this is the best description | 10 | 3654 | 10-70005 | 0.3 | 40 | 12 | 182 | 9631 | (null) | (null) | (null) | (null) | (null) | (null) |
| 10-70005 | bill murray | this is the best description | 10 | 3655 | 10-70005 | 0.5 | 40 | 20 | 182 | 9632 | (n

discount was joining to billable and only billable. But, since no rows of discount matches a row in billable, all billable rows show up as if there were no matches to project.

One workaround is to force the FULL JOIN first by placing it in a WITH clause.

with billable_discount as (
   select *
   from billable
    full join discount 
     on billable.bindex = discount.dindex
)
select * from project
  left join billable_discount
    on project.pproject = coalesce(billable_discount.bproject, billable_discount.dproject)
;

StackExchange Database Administrators Q#201816, answer score: 2