Union query


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: Union query

  1. #1
    Join Date
    Mar 2006
    Posts
    20

    Union query

    Will you help me built a union query ? I have 2 queries, qryinput and qryoutput.Out of these query i have a third query called qryDiff substracting these
    queries. However qryDiff does not show all the goods sold, only those goods that are substracted.Therefore i want to build an union query but somehow i cannot do it. Will you help ?

    My first query, qryinput is :
    SELECT [order details].ProductID, Sum([order details].Quantity) AS Sum1, orders.orderid
    FROM (orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN products ON [order details].ProductID = products.Productid
    GROUP BY [order details].ProductID, orders.orderid;
    My second query, qryoutput is :

    SELECT [order details].ProductID, orders.orderid, [order details].Quantity AS Sum2
    FROM ((orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID) INNER JOIN Customers ON orders.customerid = Customers.Customerid) INNER JOIN products ON [order details].ProductID = products.Productid;

    My thirs query, qryDiff is :

    SELECT qryProducts.ProductID, Sum(qryInput.Sum1) AS imported, Sum(qryOutput.Sum2) AS exported
    FROM (qryProducts LEFT JOIN qryInput ON qryProducts.ProductID = qryInput.ProductID) LEFT JOIN qryOutput ON qryProducts.ProductID = qryOutput.ProductID
    GROUP BY qryProducts.ProductID;

    Now i want to convert the query qryDiff into an union query, but i cannot do it.I somehow managed to build a simple query showing all the products:
    SELECT ProductID
    FROM qryInput
    UNION SELECT ProductID
    FROM qryOutput;
    This query shows the productsid indeed, but i cannot add the other items form the qryinput and qryoutput as the sum etc.
    I also i cannot convert my query qryDiff into an uinion query, with fields for the imported and the exported sums and also for the product names.
    Where is my error and now could i achieve my aim? My aim is that in the query qryDiff to show all the products, and not only the products sold.


    I will be very grateful for any comments

  2. #2
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    Moved to Database Forum
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  3. #3
    Join Date
    Jul 2007
    Posts
    161
    A subselect would work better.

    SELECT ProductID, (Select Sum([order details].Quantity) FROM [order details] WHERE [order details].ProductID = Products.ProductID) AS Quantity
    FROM Products

    To get import and export data you need a way to identify the difference. Maybe that is what you are doing with the customer table (that is not good design).

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Wouldn't you need a GROUP BY productid with that?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  5. #5
    Join Date
    Jul 2007
    Posts
    161
    Productid should be unique in the products table.

Similar Threads

  1. Replies: 0
    Last Post: 11-30-2006, 05:58 AM
  2. Replies: 5
    Last Post: 08-18-2006, 01:15 PM
  3. Large UNION query trunctates data in DB field
    By Jason Salas in forum ASP.NET
    Replies: 1
    Last Post: 08-18-2003, 07:49 AM
  4. Get 10 months forward with select statment
    By Nirit Touboul in forum Database
    Replies: 7
    Last Post: 02-25-2001, 11:34 AM
  5. SQL query with UNION
    By Charles in forum VB Classic
    Replies: 1
    Last Post: 10-06-2000, 03:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center