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 |
Best Answer
It is possible to specify your joins in such a way that logically the join between
billable
anddiscount
happens first. Performing that join first will let you outer-join the resulting set toproject
matching the latter'spproject
against eitherbproject
ordproject
, so thatproject
rows are always populated in the output.There are two ways to implement that. The first option uses the nested join syntax:
You can optionally put brackets around the full join to make the intent clearer to the reader:
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:
This achieves the same logical sequence of joins: first
billable
withdiscount
, then their join result withproject
, theproject
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, andbillable
,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.