PLEASE HELP! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

PLEASE HELP!

Author

Topic

adama
Starting Member

2 Posts

Posted - 2010-04-13 : 17:19:01

Ok, so im very new to SQL and I cannot figure out the following question im supposed to answer on a homework assignment:

"Show the FirstName and LastName of all customers who have had an order with an Item named ‘Dress Shirt’. Use a Join. Present results sorted by LastName in ascending order and then FirstName in descending order."

I have 3 tables with the following columns:
Customer(CustomerID,FirstName,LastName,Phone,Email)
ORDER_ITEM(InvoiceNumber,ItemNumber,Item,Quantity,UnitPrice)
ORDERS(InvoiceNumber,CustomerID,DateIN,DateOUT,TotalAmount)

*'Dress Shirt' is listed in the Item column in the ORDER_ITEM table

I dont understand how to write a join for this using 3 tables and the fact that I dont have a similar column in all three tables, although each table shares a common column with another.

someone please help me!

adama
Starting Member

2 Posts

Posted - 2010-04-13 : 17:26:40

I have tried following so far:

SELECT FirstName, LastName
FROM Customer, ORDERS, ORDER_ITEM
WHERE Customer.CustomerID=ORDERS.CustomerID AND ORDER_ITEM.ITEM= ‘Dress Shirt’
ORDER BY LastName ASC, FirstName DESC

SELECT FirstName, LastName
FROM Customer, ORDERS, ORDER_ITEM
WHERE Customer.CustomerID=ORDERS.CustomerID AND ORDER_ITEM.InvoiceNumber=ORDERS.InvoiceNumber AND ORDER_ITEM.ITEM= ‘Dress Shirt’
ORDER BY LastName ASC, FirstName DESC

as well as some others that have shown me no results...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-13 : 20:07:59

Does Access support this JOIN syntax:?

SELECT c.FirstName, c.LastName
FROM Customer c
JOIN ORDERS o
ON c.CustomerID = o.CustomerID
JOIN ORDER_ITEM oi
ON o.InvoiceNumber = oi.InvoiceNumber
WHERE oi.ITEM = 'Dress Shirt'
ORDER BY c.LastName ASC, c.FirstName DESC

Your second query should be equivalent to the above though.

Are you sure that there is linking data for the joins? Can you show us sample data of the linking data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources