Querying 5 tables: SQL using MS query

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • laurastevens
    New Member
    • Nov 2006
    • 1
    #1

    Querying 5 tables: SQL using MS query

    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.
    Tags: None
    • ronverdonk
      Recognized Expert Specialist
      • Jul 2006
      • 4259
      #2
      If you are talking MSSQL you are in the wrong forum!

      Ronald :cool:

        Comment

        • willakawill
          Top Contributor
          • Oct 2006
          • 1646
          #3
          Originally posted by ronverdonk
          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???

            Comment

            • willakawill
              Top Contributor
              • Oct 2006
              • 1646
              #4
              Originally posted by laurastevens
              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:
              Code:
              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

                Comment

                Previous template Next
                Working...