How Do I Force Data From First Table to Display

Question

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 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |      0.2 |      50 |       10 |      182 |   2607 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
|   (null) |      (null) |                       (null) |  (null) | (null) |   (null) |   (null) |  (null) |   (null) |   (null) | (null) | 4344762 | 10-70005 |      7 |      56 |      804 |   7875 |
|   (null) |      (null) |                       (null) |  (null) | (null) |   (null) |   (null) |  (null) |   (null) |   (null) | (null) | 4346345 | 10-70005 |      4 |      32 |      804 |   9633 |
|   (null) |      (null) |                       (null) |  (null) | (null) |   (null) |   (null) |  (null) |   (null) |   (null) | (null) | 4346678 | 10-70005 |      2 |      10 |      804 |   1450 |

This is the result I need:

| 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 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |      0.2 |      50 |       10 |      182 |   2607 |  (null) |   (null) | (null) |  (null) |   (null) | (null) |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |   (null) |  (null) |   (null) |   (null) | (null) | 4344762 | 10-70005 |      7 |      56 |      804 |   7875 |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |   (null) |  (null) |   (null) |   (null) | (null) | 4346345 | 10-70005 |      4 |      32 |      804 |   9633 |
| 10-70005 | bill murray | this is the best description |      10 |   3061 | 10-70005 |   (null) |  (null) |   (null) |   (null) | (null) | 4346678 | 10-70005 |      2 |      10 |      804 |   1450 |

Answer 1

It is possible to specify your joins in such a way that logically the join between billable and discount happens first. Performing that join first will let you outer-join the resulting set to project matching the latter's pproject against either bproject or dproject, so that project rows are always populated in the output.

There are two ways to implement that. The first option uses the nested join syntax:

SELECT
  *
FROM
  dbo.project AS p
  LEFT JOIN
    dbo.billable AS b
    FULL JOIN dbo.discount AS d ON b.bindex = d.dindex
  ON p.pproject IN (b.bproject, d.dproject)
;

You can optionally put brackets around the full join to make the intent clearer to the reader:

...
FROM
  dbo.project AS p
  LEFT JOIN
  (
    dbo.billable AS b
    FULL JOIN dbo.discount AS d ON b.bindex = d.dindex
  )
  ON p.pproject IN (b.bproject, d.dproject)

The syntax itself, however, is unambiguous enough as it is (to the parser anyway).

It should be noted that the nested join syntax is not universally popular. If you like, there is an alternative that involves no nesting – a right join:

SELECT
  *
FROM
  dbo.billable AS b
  FULL JOIN dbo.discount AS d ON b.bindex = d.dindex
  RIGHT JOIN dbo.project AS p ON p.pproject IN (b.bproject, d.dproject)
;

This achieves the same logical sequence of joins: first billable with discount, then their join result with project, the project rows being on the outer side the join, i.e. they will always be present in the output, same as with the previous query.

The only difference is that if you use SELECT * in both cases, the order of columns in the output will not be the same. Columns will be listed based on the order of tables as mentioned in the query: project columns, billable columns, discount columns in the nested query output, and billable, discount, project in the right join one.

But using * in production is rarely a good idea anyway. Thus, if you specify the output columns explicitly, there should be no difference at all between the two options, especially if you take into account that a right join is not very popular among developers either.

Answer 2

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)
;