Results 1 to 6 of 6
  1. #1
    AirborneFluff is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2021
    Posts
    2

    Making an INNER JOIN before a UNION ISSUE

    I have got 2 tables which are 'union' together to produce a set of records. This is fairly simple, here is the SQL
    Code:
    SELECT TOP 31 *
    FROM
    (
    SELECT * FROM tblOrderItems
    UNION ALL
    SELECT * From tblDummy
    )
    Basically, this is to populate a report with 31 entries, regardless of how many real entries there are. Blank entries are present so that when printing a copy of the form, entries can be added my hand as well as the digital information

    My issue is now when I want to pull information from other tables using INNER JOIN, it gives me the error 'Syntex error in JOIN operation'.
    I'm new to SQL, so, naturally, I just copied the SQL from a MS Access created query and applied it into my previous code. Here is my problematic code
    Code:
    SELECT TOP 31 *
    FROM
    (
    SELECT * FROM tblOrderItems INNER JOIN (tblOrderItems INNER JOIN tblOrders ON tblOrderItems.orderID = tblOrders.ID) ON tblSuppliers.supplierID = tblOrders.supplierID
    UNION ALL
    SELECT * From tblDummy
    )
    I expected this to pull the extra data in from the other table.
    Any help would be greatly appreciated!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Union ALL will bring in duplicates. You may just want UNION.

    I think we'll need more info or some sample data to offer focused response.

    What is it that determines a TOP record? Date, ID, some Amount, other condition?

  3. #3
    AirborneFluff is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2021
    Posts
    2
    UNION ALL is what I need. So the tblDummy is table of 31 blank rows containing the same fields as the tblOrderItems.
    These fields are simply things like item, quantity, price... And the things I want to link, supplierID and orderID
    These 2 values reference the unique IDs of the order held in another table (tblOrders) and of the supplier (tblSuppliers). These tables contain additional information about the order which I want to link into my report (such are suppliers name)

    The TOP record is the top 31 of the field itemIndex, which is simply the position of the item in the order

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    tblSuppliers is missing from your joins and tblOrderItems is entered twice. Try this:

    Code:
    SELECT TOP 31 *
    FROM
    (
    SELECT * FROM tblOrderItems INNER JOIN (tblSuppliers INNER JOIN tblOrders ON tblOrderItems.orderID = tblOrders.ID) ON tblSuppliers.supplierID = tblOrders.supplierID
    UNION ALL
    SELECT * From tblDummy 
    )
    Still not sure you want UNION ALL if tblDummy consists of 31 blank rows
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Lhoj is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2021
    Posts
    92
    Quote Originally Posted by isladogs View Post
    tblSuppliers is missing from your joins and tblOrderItems is entered twice. Try this:

    Code:
    SELECT TOP 31 *
    FROM
    (
    SELECT * FROM tblOrderItems INNER JOIN (tblSuppliers INNER JOIN tblOrders ON tblOrderItems.orderID = tblOrders.ID) ON tblSuppliers.supplierID = tblOrders.supplierID
    UNION ALL
    SELECT * From tblDummy 
    )
    Still not sure you want UNION ALL if tblDummy consists of 31 blank rows
    Mmmm. might be totally wrong and it might be just me doing that all this time because I thought it was necessary, but aren't you supposed to nest the ON clauses in order too? In which case
    Code:
    SELECT TOP 31 *
    FROM
    (
    SELECT * FROM tblOrderItems INNER JOIN (tblSuppliers INNER JOIN  tblOrders ON tblSuppliers.supplierID = tblOrders.supplierID) ON  tblOrderItems.orderID = tblOrders.ID
    UNION ALL
    SELECT * From tblDummy 
    )
    If that's not the case and you can set the on clauses willingly someone please tell me because that will be pretty useful to know in the future.

    Anyway, don't think a union with different columns will ever work, and Dummy has the same fields as orders so it will never match orders joined with whatever else. So either join dummy with the same tables (which will result in 0 records with inner joins if Dummy is full of blank records) or add some select nulls to the dummy side of the union to match the columns from the first one. Either way, same as Colin, can't figure out why you wanna attach dummy table to the query if it has no data, unless it's for future use of said table.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Maybe (I couldn't test it):
    Code:
    SELECT TOP 31 FROM
    (
    SELECT 0 AS xx, oi.* FOM (tblOrderItems oi INNER JOIN tblOrders ord ON ord.OrderID = oi.OrderID) INNER JOIN tblSuppliers supp ON supp.SupplierID = ord.SupplierID
    UNION ALL
    SELECT 1 AS xx, d.* FROM tblDummy d
    )
    OREDER BY xx ASC [AND oi. ...]
    Added field xx, and used it to order results ensures, that dummy rows are added only when first part returns less than 31 rows. You can add additional order conditions [the part between '[]')to ensure that in case the first part returns more than 31 rows, the right set of rows is returned.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL Join and Union query on the same table
    By waterpower in forum Queries
    Replies: 3
    Last Post: 01-23-2021, 11:28 AM
  2. Inner Join in Union Query
    By RAJESHKUMAR R in forum Queries
    Replies: 1
    Last Post: 02-25-2018, 12:36 AM
  3. Union query to join two crosstab queries
    By racefan91 in forum Queries
    Replies: 5
    Last Post: 09-26-2013, 10:24 AM
  4. Unequal Join or Union?
    By cap.zadi in forum Queries
    Replies: 3
    Last Post: 09-23-2011, 05:57 AM
  5. Union query (or Inner/Outer join?)
    By LilMissAttack in forum Queries
    Replies: 4
    Last Post: 10-23-2010, 12:36 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums