Joining two tables where Invoice # not unique

[dg3249]

I need help in joining two tables where the invoice # is not unique. The same invoice number can exist in different branches.

Invoice_Header
-Invoice_Number -Invoice_Total -Branch
-79322 -259.00 -11051
-79322 -450.00 -32165

Invoice_Lines
-Invoice_Number -Hrs -Branch
-79322 -6.0 -11051
-79322 -36.5 -32165

This is a sample of my query..

from invoice_lines Right Outer Join
Invoice_header on invoice_lines.invoice_number = Invoice_header.invoice_number

My query is pulling the invoice numbers and all data from Invoice_Lines correctly but it is not pulling the data from Invoice_Header properly. It only brings in the first invoice total amount of 259.00 and does not show the other total. Any suggestions would be helpfull.

Diana

 

[grahams]

If an invoice number is only unique within a branch, then you need to do your join on both the invoice number and branch number:
FROM invoice_header ih join invoice_lines il
on ih.invoice_number - il.invoice_number
AND ih.Branch = il.Branch

HTH

Graham

 

[mwolf00]

Join On invoice_number and branch


SELECT field1, field2 FROM
invoice_header IH
JOIN invoice_lines IL ON IH.invoice_number = IL.invoice_number AND IH.branch = IL.branch -----------------------------------------------------------------
"The difference between 'involvement' and 'commitment' is like an eggs-and-ham breakfast: the chicken was 'involved' - the pig was 'committed'."
- unknown

mikewolf@tst-us.com

 

[dg3249]

I've done what you suggested and it seems to not add the entry with the second branch, it only includes an entry for the first branch.

 

[grahams]

Can we have the whole query please?

 

[dg3249]

I have posted my whole query so that you can get a better idea.

select dbo.NA_invoice_lines.hours_worked, dbo.NA_Invoice_lines.billing_amt,dbo.NA_invoice_lines.branch_number,
dbo.NA_invoice_lines.invoice_number, dbo.NA_invoice_header.client_name,
dbo.NA_invoice_header.invoice_net_amt, dbo.NA_invoice_header.invoice_total,
convert(datetime, dbo.NA_Invoice_lines.week_ending)AS Week_ending, timesheet_number

from dbo.NA_invoice_lines Inner Join
dbo.NA_Invoice_header on dbo.NA_invoice_lines.invoice_number = dbo.NA_Invoice_header.invoice_number
and dbo.NA_invoice_lines.branch_number = dbo.NA_invoice_header.branch_number