CODEWITHSUNDEEP
sqlms-access
tc90
tc90

Reputation: 307

Subquery in MS-Access

I need to create an SQL statement that will get the following information: ISBN, Quantity, OrderTotal, FirstName, LastName, PhoneNumber, and StreetAddress from multiple tables.

Here are the two queries i have so far that give me the information i need:

CustomerInfo

SELECT c.CustomerID, c.FirstName, c.LastName, c.PhoneNumber, a.StreetAddress
FROM Customers AS c, Address AS a
WHERE c.CustomerID = a.CustomerID;

OrderInfo

SELECT o.CustomerID, o.OrderTotal, i.ISBN, i.Quantity
FROM Orders AS o, OrderItems as i
WHERE o.OrderID = i.OrderID;

CombinedQuery This doesn't work!!!

SELECT c.FirstName, c.LastName, c.PhoneNumber, c.StreetAddress, o.OrderTotal, o.ISBN, o.Quantity
FROM CustomerInfo as c, OrderInfo as o
WHERE c.CustomerID = o.CustomerID;

How do i join the first two queries into one query that will provide the same information that the third query should provide?

Upvotes: 0

Views: 60

Answers (2)

Wolfie
Wolfie

Reputation: 1381

SELECT c.CustomerID, c.FirstName, c.LastName, c.PhoneNumber, 
a.StreetAddress, 
o.CustomerID, o.OrderTotal, 
i.ISBN, i.Quantity
FROM Customers AS c, Address AS a, Orders AS o, OrderItems as i
WHERE o.OrderID = i.OrderID;
AND c.CustomerID = a.CustomerID
AND c.CustomerID = o.CustomerID;

Try that as one big query instead of doing two and then combining them. This will join together all four of your tables.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

There is no CustomerId field in OrderInfo. You need all three tables

SELECT c.FirstName, c.LastName, c.PhoneNumber, c.StreetAddress,
       oi.OrderTotal, oi.ISBN, oi.Quantity
FROM (Customers as c INNER JOIN
      Orders AS o
      ON c.CustomerID = o.CustomerID
     ) INNER JOIN
     OrderItems as oi
     ON o.OrderID = oi.OrderID;

You also need to learn proper join syntax with an on clause. Implicit joins in the where clause are outdated, less expressive, and more prone to error.

Upvotes: 1

Related Questions