SQL Query with the Northwind database (Catch: no Inner Join)

Question

First and foremost - it is a homework assignment, yet I am not asking for anyone to do it. Instead I need help with how to construct the query.

As part of an assignment, using the Northwind database via Microsoft Access, I have to construct this query:

The product ID, product name, and quantity ordered for all products ordered on an order taken by an employee with last name Fuller.

Now, when I construct the query in design mode, this is the code I get:

SELECT Products.ProductID, Products.ProductName, [Order Details].Quantity
FROM Products INNER JOIN ((Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Employees.LastName)="Fuller"));

As I mentioned earlier, we are not allowed to use Inner Join. After scouring the text/notes thus far I can't find how to go about it. (Very new to SQL)

Would I be rewriting the FROM statement? And if so, do the SELECT and WHERE statements change?

UPDATE: Here is the code as I rewrote it:

SELECT Orders.OrderID, Orders.CustomerID, Orders.ShipCity
FROM Products, Orders, [Order Details], Employees
WHERE
  Products.ProductID = [Order Details].ProductID
  AND Employees.EmployeeID = Orders.EmployeeID
  AND Ordes.OrderID = [Order Details].OrderID
  AND Employees.LastName = "Fuller";

I'm having a sort of syntax issue though. Since the Order Details must be enclosed in brackets, it wants to ask for parameter values for what I wrote in the WHERE statement.

An `IN ()` subquery like `WHERE somecolumn IN (SELECT somecommonvale FROM othertable)` is often a possibility, but more complicated when dealing with more than 2 tables (you appear to have 4) — Michael Berkowski, Jan 20 '15 at 18:40
I do. Because of the way the tables are set up, the Product's ID and name can be pulled from the Product table, but the quantity ordered must go through Orders -> Order details, because the Employee name (Fuller is what I am looking for) is in the Orders table, while the quantity is in the Order Details table. — Monobus, Jan 20 '15 at 18:42
Well, it could be rewritten using multiple _nested_ `IN (SELECT...)` subqueries, but this is a peculiar request because the presence of columns from more than one table in the `SELECT` list implies some sort of join. Is it possible what's being asked is to use an _implicit join_, where multiple tables are listed in the `FROM` clause and related in `WHERE` like `FROM Products, Orders, [Order Details], Employees WHERE Products.ProductID = [Order Details].ProductID AND....` — Michael Berkowski, Jan 20 '15 at 18:46
Doing it that way does not explicitly use `INNER JOIN` but still manages to join the tables using an older joining syntax... — Michael Berkowski, Jan 20 '15 at 18:46
Okay, trying that now. I'm trying to track my professors reasoning...is there a reason you wouldn't want to use Inner Join? — Monobus, Jan 20 '15 at 19:01
I can see wanting you to understand the `IN (SELECT...)` method, but that isn't really available to you with so many tables and a complex SELECT list. Using the implicit (comma separated FROM) joining syntax is generally not preferable to explicit INNER JOINs, but you may be intended to learn it. — Michael Berkowski, Jan 20 '15 at 19:03
The basic format (and pros/cons) is discussed here: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Michael Berkowski, Jan 20 '15 at 19:05
Also: I have rewritten it the way you suggested, but for whatever reason Order Details was in brackets. When I leave it in brackets, I of course get the input window for orderID, but when I remove them it gives a syntax error. Is this because the name is two words? — Monobus, Jan 20 '15 at 19:08
That I can't really answer - Did you include _all_ the relationships in the `WHERE`? If the order Id is ambiguous, Access may prompt for it, but if all the joins are represented then it shouldn't be ambiguous. It _must_ be bracketed though. — Michael Berkowski, Jan 20 '15 at 19:18
@Monobus The latest revision includes `Ordes.OrderID` Does your query work after you correct that table name? — HansUp, Jan 20 '15 at 19:31
Typo here - `Ordes` instead of `Orders` -- maybe that's what access is prompting? `AND Ordes.OrderID` — Michael Berkowski, Jan 20 '15 at 19:31
@HansUp That was it! Wow, silly me. I appreciate the point out. — Monobus, Jan 20 '15 at 19:34
@MichaelBerkowski I really appreciate the help in showing me the explicit joining and also pointing our my error. Hopefully I can figure out the rest of the queries no problem. — Monobus, Jan 20 '15 at 19:35
@Monobus I'm not sure this fulfills your instructor's requirement, but I'll summarize this as an answer below. — Michael Berkowski, Jan 20 '15 at 19:36

score 3 · Accepted Answer · edited May 23 '17 at 12:20

The requirement not to use INNER JOIN is a little peculiar and I cannot guarantee this is what was intended for your assignment.

One way to alternatively write a simplistic relationship between two tables is with an IN () subquery, the general form being:

SELECT *
FROM a_table
WHERE some_column IN (
  SELECT some_related_column
  FROM b_table
  WHERE some_condition
)

But since you must represent columns from more than one of your 4 tables in the SELECT list, that form isn't going to work for you. Another way of not explicitly using an INNER JOIN is to use the older implicit joining syntax, in which multiple tables are listed in the FROM clause separated by commas, and their joining conditions placed in the WHERE clause instead of in ON.

The general form is:

SELECT 
  a_table.col1,
  a_table.col2,
  b_table.col1
FROM
  a_table,
  b_table
WHERE
 a_table.some_column = b_table.some_related_column
 AND some_other_conditions

It being an assignment, I will leave it to you to work out the entire statement, but yours would take a form like

SELECT
  Orders.OrderID,
  Orders.CustomerID
FROM 
  Products,
  Orders,
  [Order Details],
  Employees
WHERE
  Products.ProductID = [Order Details].ProductID 
  AND ... (the other table relationships)
  AND Employees.LastName = 'Fuller'

Although it is functionally identical to explicit INNER JOINs, this is an older syntax, and is often discouraged nowadays. More discussion is available in this question

Considering the prompt was concise and the only disclaimer was not to use Inner Join, I feel like this was correct. I've already knocked out a few more queries thanks to your pointers. — Monobus, Jan 20 '15 at 19:48

SQL Query with the Northwind database (Catch: no Inner Join)

1 Answers1