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.
3  2570 
If you are talking MSSQL you are in the wrong forum!
Ronald :cool:
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???
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: -
SELECT [Customer].[Customer No]
-
, [Sales Invoice].[Order Date]
-
, [Sales Invoice].[Order No]
-
, [Sales line].[Qty]
-
, [Sales line].[Unit Price]
-
, [Sales line].[Line Amt]
-
FROM [Customer] C
-
INNER JOIN [Sales Invoice] S
-
ON C.[Customer No] = S.[Customer No]
-
INNER JOIN [Sales line] L
-
ON C.[Customer No] = L.[Customer No]
-
-
Hope this helps
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Elliot Rodriguez |
last post by:
As I continue to read more about the benefits of database querying using
ADO.NET, I am having a more difficult time distinguishing what the best
approach to data retrieval is anymore.
When...
|
by: Steve |
last post by:
Hi;
I am looking for suggestions about how to solve a problem using tsql.
I have been asked to create a report concerning 4 tables.
Each of the 4 tables is in its own database.
The 4...
|
by: Greg |
last post by:
I am working on a project that will have about 500,000 records in an XML
document. This document will need to be queried with XPath, and records
will need to be updated. I was thinking about...
|
by: Shane |
last post by:
I wonder if someone has any ideas about the following.
I am currently producing some reports for a manufacturing company who work
with metal.
A finished part can contain multiple sub-parts to...
|
by: roiavidan |
last post by:
Hi,
I'm having a bit of a problem with a small application I wrote in C#,
which uses an Access database (mdb file) for storing financial data.
After looking for a similiar topic and failing to...
|
by: loosecannon_1 |
last post by:
I get a 90-120 second blocking when send 15 or so simultaneous queries
to SQL Server 2000 that query a view made up of two joined tables.
After each query is blocking for the same amount of time...
|
by: sql_er |
last post by:
Guys,
I have an XML file which is 233MB in size. It was created by loading 6
tables from an sql server database into a dataset object and then
writing out the contents from this dataset into an...
|
by: chitownjohn69 |
last post by:
I'm working on a simple database that has 2 tables one called ITEMS, one called USERS. I'm tracking items being transfered between 2 users. in the ITEMS table I reference the sending user and...
|
by: SilRay |
last post by:
I will admit up front that this is to help with an assignment for a class, however, I'm looking for information outside the scope of the assignment because I want to do something cooler. The...
|
by: mcmahon |
last post by:
Hi,
I have a MS Access DB with a query that I need to display in vb but am not too sure on how to go about it. The query has some criteria - i.e you have to input a date to run the query. I think...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
