LIST with subqueries -->Problem

[jonathan.cohen150887]

Hi,

Ok here is the problem:

I have the following task to do:

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

Problem is I cant seem to udnerstand how subqueries work so Im totally
lost since the info comes out of at least 3 tables.

Here are my tables:

AUTHOR : author_ref , FNAME, LNAME
Book_Author: ISBN_ref, Author_ref -->as multiple books can be written
by multiple authors
CustOrders_Books: CustOrder_ref, ISBN_ref, QUANTITY
CustomerOrders: CustOrder_ref, Customer_ref

So Im suppose to list the mby Customer_ref.

Can anyone help me ?thanks

 

[KARL DEWEY]

No subqueries needed.
SELECT CustomerOrders.Customer_ref, Book_Author.Author_ref,
Count(Book_Author.Author_ref) AS CountOfAuthor_ref,
Sum(CustOrders_Books.QUANTITY) AS SumOfQUANTITY, AUTHOR.FNAME, AUTHOR.LNAME
FROM ((CustomerOrders LEFT JOIN CustOrders_Books ON
CustomerOrders.CustOrder_ref = CustOrders_Books.CustOrder_ref) LEFT JOIN
Book_Author ON CustOrders_Books.ISBN_ref = Book_Author.ISBN_ref) LEFT JOIN
AUTHOR ON Book_Author.Author_ref = AUTHOR.author_ref
GROUP BY CustomerOrders.Customer_ref, Book_Author.Author_ref, AUTHOR.FNAME,
AUTHOR.LNAME
HAVING (((Count(Book_Author.Author_ref))>1));

 

[jonathan.cohen150887]

Thanks. Modified the spelling mistakes and here it is. But it still
asks me for an author_Ref value that I ahve to enter whereas it should
do it automatically, how do you do this?

SELECT CustomerOrders.Customer_ref, Book_Author.Author_ref,
Count(Book_Authors.Author_Ref) AS CountOfAuthor_Ref,
Sum(CustOrders_Books.QUANTITY) AS SumOfQUANTITY, Authors.AUT_FNAME,
Authors.AUT_LNAME
FROM ((CustomerOrders LEFT JOIN CustOrders_Books ON
CustomerOrders.CustOrder_Ref = CustOrders_Books.CustOrder_Ref) LEFT
JOIN
Book_Author ON CustOrders_Books.ISBN_Ref = Book_Author.ISBN_Ref) LEFT
JOIN
Authors ON Book_Author.Author_Ref = Authors.author_Ref
GROUP BY CustomerOrders.Customer_Ref, Book_Author.Author_Ref,
Authors.AUT_FNAME,
Authors.AUT_LNAME
HAVING (((Count(Book_Author.Author_Ref))>1));

Thanks