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  2577 
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 creating datasets in the Visual Studio.NET designer, Command objects
(Update, Insert, Delete) are automatically generated for you based off your
SelectCommand value. You then get the benefits of strong typing, which from
what I've seen so far are...
|
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 tables are identical in name and structure.
|
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 splitting up the XML into
several XML documents (perhaps 50,000 per document) to be more efficient but
this will make things a lot more complex because the searching needs to go
accross all 500,000 records. Can anyone point me to some best practices...
|
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 make up the finished part.
The sub-parts can also be made up of sub-parts and those sub-parts can also
be made up of sub-parts etc etc.
All parts are contained within the same table and I have a seperate table
|
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 find one, I'm posting
the question in hope some one of you guys out there will have the
answer for me...!
I'll start with what I have, then I'll continue to the problem itself.
| | |
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 they all
return. Further identical queries of this type work in 3-4 seconds
(caching?) until hours later where it happens again. If I query the
tables directly (without the view) I still get the same blocking. If I
remove the join (it is a simple...
|
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 XML file.
Once my application starts, I load this XML file into a DataSet object
using "ReadXML" function. This creates a dataset in memory with 6
tables.
|
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 receiving user by a numeric field ITEMUSERID and SENDTO that both refer to the USERID in the USERS table. I want to pull the description of both of these users in my result. I am able to do this using a stored query and then querying that query. I want...
|
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 assignment is intended to demonstrate the ability to query against a joined table. I am supposed to get the values for all fields of a given datatype within these 2 tables that are joined. The teacher expects that we will look at the descriptions of 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 that's a parameter in vb......
Well I've been playing around with it for some time now but amn't really getting anywhere. Could ye recommend any good tutorial or link as to how to do it?
Here's the code that I've been working on:
Imports...
|
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
