By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,996 Members | 1,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,996 IT Pros & Developers. It's quick & easy.

Querying 5 tables: SQL using MS query

P: 1
I have the following tables with the following fields I need from each of these tables.

Customer table: fields: Customer Name, Customer No.

Sales Header table: fields: Customer No., Order Date, Job No.
Sales Invoice Header table: Customer No., Order Date, Order No., Job No.

Sales line table: Customer No.,Type, Qty, Unit Price, Line Amt, Job No.
Sales Invoice Line table: Customer, No., Type, Qty, Unit Price, Line Amt., Job No.
(the only difference between these last 2 tables is one is open orders and the other is closed orders so I need my query to pull all. I assume I need a "Union All" but I dont' know how to incorporate or get the needed links/information from the other 3 databases.

The fields I need to see upon running the query are:
Customer Name, Customer No., Order Date, Order No., Qty, Unit Price, Line Amt.

How do I get my SQL statement correct to pull the information from each database correctly. Thanks in advance. I've been working on this for 2 days and felt like I got close but no cigar.
Nov 17 '06 #1
Share this Question
Share on Google+
3 Replies


ronverdonk
Expert 2.5K+
P: 4,258
If you are talking MSSQL you are in the wrong forum!

Ronald :cool:
Nov 18 '06 #2

100+
P: 1,646
If you are talking MSSQL you are in the wrong forum!

Ronald :cool:
Hey Ronald, you lost me here. This is not MS SQL Server forum???
Nov 18 '06 #3

100+
P: 1,646
I have the following tables with the following fields I need from each of these tables.

Customer table: fields: Customer Name, Customer No.

Sales Header table: fields: Customer No., Order Date, Job No.
Sales Invoice Header table: Customer No., Order Date, Order No., Job No.

Sales line table: Customer No.,Type, Qty, Unit Price, Line Amt, Job No.
Sales Invoice Line table: Customer, No., Type, Qty, Unit Price, Line Amt., Job No.
(the only difference between these last 2 tables is one is open orders and the other is closed orders so I need my query to pull all. I assume I need a "Union All" but I dont' know how to incorporate or get the needed links/information from the other 3 databases.

The fields I need to see upon running the query are:
Customer Name, Customer No., Order Date, Order No., Qty, Unit Price, Line Amt.

How do I get my SQL statement correct to pull the information from each database correctly. Thanks in advance. I've been working on this for 2 days and felt like I got close but no cigar.
Hi
There is a lot of redundancy in these tables. Same data in each.
To get the result that you want you only need to use 3 of the tables:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Customer].[Customer No]
  2. , [Sales Invoice].[Order Date]
  3. , [Sales Invoice].[Order No]
  4. , [Sales line].[Qty]
  5. , [Sales line].[Unit Price]
  6. , [Sales line].[Line Amt]
  7. FROM [Customer] C
  8. INNER JOIN [Sales Invoice] S
  9. ON C.[Customer No] = S.[Customer No]
  10. INNER JOIN [Sales line] L
  11. ON C.[Customer No] = L.[Customer No]
  12.  
  13.  
Hope this helps
Nov 18 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.