Web Development

Ask something
Question Locked

SQL query (MS Access)

0 helpful
10 replies

by benmidgley

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,

I am trying to learn how to use SQL and am completely stuck atm. Any help at all would be most welcomely received. I have stared at this for so long that it isn't even funny any more :) [Apologies for being a total noob].

I have created a database which represents an online bookstore. I would like to complete the following two queries.

1. Output a list of bookgenres in order of popularity.

I have attempted it as follows but with no love .

SELECT CAT.Genre_code, GEN.Description, COUNT (CUSTORDPROD.ISBN) AS Rank
FROM GEN, CAT, CUSTORDPROD
WHERE GEN.Genre_code = CAT.Genre_code
AND CAT.ISBN = CUSTORDPROD.ISBN
GROUP BY CUSTORDPROD.ISBN, GEN.Genre_code
ORDER BY GEN.Genre_code DESC;

2. List everyone who has bought two or more books by the same author.

SELECT CUST.Customer_name, CUSORDS.Order.ID, AUTH.Author_ID, AUTH.First_Name, AUTH.Last_name
FROM CUST, CUSORDS, CUSTORDPROD, BOOKAUTH, AUTH
WHERE CUST.Customer_ID = CUSORDS.Customer_ID
AND CUSORDS.Order_ID = CUSTORDPROD.Order_ID
AND CUSTORDPROD.ISBN = BOOKAUTH.ISBN
AND BOOKAUTH.Author_ID = AUTH.Author_ID
AND AUTH.Author_ID =
(SELECT CUSORDS.Order_ID
AND CUSTORDPROD.ISBN
AND AUTH.Author_ID
FROM CUST, CUSTORDPROD, AUTH
WHERE Author_ID >2);

The frustrating thing is knowing that there is something very simple that I'm just failing to grasp. Argh!

Thanks in advance for any help!

10 replies

by john.a.wills

If you want to order by popularity of genre, your should not tell the processor to order by genre code. Try order by Rank, although I have a suspicion that you will need more than that.

by benmidgley

Many thanks. I finally got it to work after much headscratching, chocolate and by adopting a different approach. Have an awesome weekend!

by john.a.wills

it does not matter what value ID the author has (for all I know you have many authors with IDs =< 2); what matters is count of orders with a particular author ID.

by benmidgley

Thanks for your reply. I realised later that it was a definite no no as to what I had done. I have tried again below but this isn't working either but at least I think it is closer to being along the right lines. I'm assuming that I can link a HAVING COUNT clause with an AND as below.

Any thoughts would be most helpful!

SELECT CUST.Customer_ID, CUST.Customer_name, CUSORDS.Order_ID, AUTH.Author_ID, AUTH.First_Name, AUTH.Last_name, COUNT(CUSTORDPROD.Quantity_ordered) AS Number_of_copies_bought, COUNT(AUTH.Auth.Author_ID) AS Number_of_titles_bought
FROM CUSORDS, CUSTORDPROD, CUST, AUTH
WHERE CUST.Customer_ID = CUSORDS.Customer_ID
AND CUSORDS.Order_ID = CUSTORDPROD.Order_ID
AND CUSTORDPROD.ISBN = BOOKAUTH.ISBN
AND BOOKAUTH.Author_ID = AUTH.Author_ID
GROUP BY CUST.Customer_ID, CUST.Customer_name, CUSORDS.Order_ID, AUTH.Author_ID, AUTH.Last_name, AUTH.First_Name
HAVING COUNT(CUSTORDPROD.Quantity_ordered) >=2
AND (AUTH.Author_ID) >=2;

Have a great weekend peoples!

by john.a.wills

I did something recently quite similar to this but cannot look it up at the moment because I am at home for Easter Week. My problem was to list all abbreviated names in the database and put beside each how many occurrences there were (e.g. JSmith 360, DJones 350). My solution used a nest of 3 SELECTs. I think that you too may need to nest.

by john.a.wills

SELECT CustomerID,AuthorID,counter
FROM
(SELECT OrderID,CustomerID,AuthorID,Count(AuthorID) AS Counter
FROM CusOrds LEFT JOIN CustOrdProd ON OrderID
LEFT JOIN BookAuth ON ISBN
GROUP BY AuthorID)
WHERE Counter > 1

by john.a.wills

If you want to order by popularity of genre, your should not tell the processor to order by genre code. Try order by Rank, azlthough I have a suspicion that you will need more than that.

by benmidgley

Hi,

Many thanks for your suggestion (apologies I have only just seen this). I have tried the code suggested above but receive a message saying missing operator in query expression 'OrderID LEFT JOIN....'.

Sorry to be a pain but any help would be most appreciated.

Many thanks!

by john.a.wills

I assumed you would know, but obviously you didn't understand me:
CusOrds LEFT JOIN CustOrdProd ON CusOrds.OrderID = CustOrdProd.OrderID is th full version. You will also have toexpand "ISBN" for the next join.

This discussion is closed to new replies.