UNION SELECT & INNER JOIN to join three tables (1 Viewer)

W

weavind

Registered User.

Local time

Today, 14:48

Joined

Feb 9, 2010

Messages

15

• Aug 24, 2010

• #1

I’m not sure how to word this, but I want to use the UNION SELECT command to join two tables together and then link another table to this joined query.
I have two invoice tables (InvoiceCompanyA & InvoiceCompanyB) which are identically setup. I then have a InvoiceDetail which lists all the invoiced items from both companies. I would like to create a report that lists all the invoices and details from both companies. The reason for the two tables for CompanyA & CompanyB is that the invoice numbers have to be incremental and unique to each company.

The tables are structured like below:

InvoiceCompanyA
· InvoiceNo
· JobNo
· Company
· Date
· Currency
· Total

InvoiceCompanyB
· InvoiceNo
· JobNo
· Company
· Date
· Currency
· Total


InvoiceDetail
· InvoiceNo
· Item
· StockNo
· Description
· Qty
· UnitPrice

I’d like the report to list the details as below:

InvoiceNo| Company| Date| JobNo| Item| Stock No| Description| Qty| UnitPrice

 

J

JANR

Registered User.

Local time

Today, 13:48

Joined

Jan 21, 2009

Messages

1,623

• Aug 24, 2010

• #2

Well in this de-normalized structure some thing like this maybe:

Code:Copy to clipboard

SELECT InvoiceDetail.InvoiceNo, Company, [Date], JobNo, Item, StockNo, Description, Qty, UnitPrice
FROM InvoiceCompanyA INNER JOIN InvoiceDetail ON InvoiceCompanyA.InvoiceNo = InvoiceDetail.InvoiceNo
Union All
SELECT InvoiceDetail.InvoiceNo, Company, [Date], JobNo, Item, StockNo, Description, Qty, UnitPrice
FROM InvoiceCompanyB INNER JOIN InvoiceDetail ON InvoiceCompanyB.InvoiceNo = InvoiceDetail.InvoiceNo;

Note the [] around Date. Date is a reserved word and should be avoided at all cost.

Also you store calculated values like Total in your tables, this can be caluculated at runtime by using an expression in a query.

Total: [Qty]*[UnitPrice]

JR

 

W

weavind

Registered User.

Local time

Today, 14:48

Joined

Feb 9, 2010

Messages

15

• Aug 24, 2010

• #3

The code below works - thank you.

 

J

JANR

Registered User.

Local time

Today, 13:48

Joined

Jan 21, 2009

Messages

1,623

• Aug 24, 2010

• #4

No worries

JR