Making an INNER JOIN before a UNION ISSUE

[AirborneFluff]

I have got 2 tables which are 'union' together to produce a set of records. This is fairly simple, here is the SQL

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

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!

[orange]

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?

[AirborneFluff]

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

[isladogs]

tblSuppliers is missing from your joins and tblOrderItems is entered twice. Try this:

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

[Lhoj]

tblSuppliers is missing from your joins and tblOrderItems is entered twice. Try this:

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

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.

[ArviLaanemets]

Maybe (I couldn't test it):

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.