0

I have been searching for answers on this but found none. I think it might be due to the fact that I don't know what terms to search for. Also, this is my first post, so I want to apologise if I use the incorrect formats.

I need to following output:

Invoice | Inv Date | Created by | Destination | Goods $ | Freight | Insurance $ |
33813..| 12 Dec ..| Jack  ........| Cape Town | 250.00 ..| 50.00 ..| 10.00 ...|
33814..| 12 Dec ..| Jenny .........| Durban ......| 5,000.00|  20.00 ..|  ....|


The first 5 columns are build from various columns using the Invoice column as an index.

Then I want to add the freight and insurance. This is hosted in a different table with the below layout:

InvCostID | Invoice | Cost Code | Cost Description | Value |<br/>
556 ..........| 33813 .| 1 ...............| Freight ...............| 50.00 |
559 ..........| 33813 .| 2 ...............| Insurance ...........| 10.00 |
570 ..........| 33814 .| 1 ...............| Freight ...............| 20.00 |


The problem is that I cannot just select columns to include as the Freight and insurance are in different rows. To get around this, I have created two 'sub tables'. One were the Cost code is 1 (thus, the freight table) and one with the cost code 2 (the insurance table).

Then I just select the Value from the correct table.

The problem: If one of the cost components does not exist (like the Insurance for invoice 33814), my current query excludes that invoice from the results completely. (with the above tables, my below code would only show invoice 33813.

select 
IT.Invoice as 'Invoice',
IT.InvDate as 'Inv Date',
UT.UserFullName as 'Created by',
IT.DestinationDescription as 'Destination',
IT.USDVal as 'Goods $',
FREIGHTLIST.Value as 'FREIGHT $',
INSURANCELIST.Value as 'INSURANCE $'

from InvoiceTable IT,
UserTable UT,
(select * from SundryCostTable where CostCode = 1) as FREIGHTLIST,
(select * from SundryCostTable  where CostCode = 2) as INSURANCELIST

where IT.InvDate > '2014-12-01'
and IT.UserId = UT.UserId
and IT.Invoice = FREIGHTLIST.Invoice
and IT.Invoice = INSURANCELIST.Invoice

Please help.

Thank you Nico

(I am using SQL Server Management Studio to run the query)

Zeeshan
  • 1,659
  • 13
  • 17
Nico
  • 275
  • 1
  • 3
  • 6

2 Answers2

0
select 
IT.Invoice as 'Invoice',
IT.InvDate as 'Inv Date',
UT.UserFullName as 'Created by',
IT.DestinationDescription as 'Destination',
IT.USDVal as 'Goods $',
FREIGHTLIST.Value as 'FREIGHT $',
INSURANCELIST.Value as 'INSURANCE $'

from InvoiceTable IT
join UserTable UT on IT.UserId = UT.UserId
left join SundryCostTable as FREIGHTLIST on CostCode = 1 and IT.Invoice = FREIGHTLIST.Invoice
left join SundryCostTable as INSURANCELIST on CostCode = 2 and IT.Invoice = INSURANCELIST.Invoice

where IT.InvDate > '2014-12-01'

Note the use of LEFT JOIN in order to not exclude rows from IT and UT in case there's no match with FREIGHTLIST or INSURANCELIST.

Take a look at this post to learn more about the use of JOIN(s).

Community
  • 1
  • 1
Menno
  • 12,175
  • 14
  • 56
  • 88
0

You might as well use a subquery instead of the LEFT JOIN as described by Aquillo. the following query takes care of the NULL values for FREIGHT and INSURANCE.

SELECT 
IT.Invoice as 'Invoice',
IT.InvDate as 'Inv Date',
UT.UserFullName as 'Created by',
IT.DestinationDescription as 'Destination',
IT.USDVal as 'Goods $',
ISNULL((select Top 1 Value from SundryCostTable where CostCode = 1 And SundryCostTable.Invoice=IT.Invoice),0) as 'FREIGHT $',
ISNULL((select Top 1 Value from SundryCostTable where CostCode = 2 And SundryCostTable.Invoice=IT.Invoice),0) as 'INSURANCE $'

from InvoiceTable IT,
UserTable UT

where IT.InvDate > '2014-12-01'
and IT.UserId = UT.UserId
su8898
  • 1,703
  • 19
  • 23
  • Thank you su8898. Which of the solutions (Join vs subquery) will be the most taxing on the server? The Join will obviously take up a lot of space, but should be faster. The subquery will probably be a bit slower as it has to run an additional query per row. Is this assumption correct? – Nico Dec 23 '14 at 07:36
  • If you remove the `ISNULL` part from the subquery both the join and subquery will be compiled into identical queries by the server. Subqueries are mostly treated like joins especially when unique keys are involved. So if INVOICE field in InvoiceTable is the primary key, I don't expect any difference in the performance/server load. Just that I noticed an alternative method. – su8898 Dec 23 '14 at 07:51