I am new to database, and below is one of my query with it's explanation, I feel it's too complex. It works, but I am worried the design is bad.
Provide a list of customer information for customers who purchased anything written by the most profitable author in the database.
SELECT E.id,NAMES.fname, NAMES.lname, E.address, E.city, E.state,E.country, E.email, E.phone, E.postalcode
FROM ENTITY AS E, (SELECT DISTINCT T.customer_id
FROM (SELECT DISTINCT O.order_id
FROM ORDERITEM AS O
WHERE O.bid IN (SELECT DISTINCT A.b_id
FROM AUTHOR AS A
WHERE A.name_id IN (SELECT DISTINCT NA.name_id
FROM (SELECT A.name_id, A.b_id
FROM (AUTHOR AS A JOIN NAMES AS N ON (A.name_id = N.id))
GROUP BY A.name_id) AS NA,(SELECT B.b_id AS bid, max(C.total_quantity * B.price), B.title
FROM BOOK AS B, (SELECT bid, SUM(quantity) AS total_quantity
FROM ORDERITEM AS O
GROUP BY O.bid) AS C
WHERE B.b_id = C.bid) AS BD
WHERE NA.b_id = BD.bid))) AS OID JOIN ORDER_TRANSACTION AS T ON (OID.order_id = T.order_id)) AS TID, NAMES
WHERE TID.customer_id = E.id AND
NAMES.id = E.name_id
And This is my design, look very simple.
Best Answer
The answer is: divide to conquer! Each sub-query should become a query (or a view) of its own. Use sub-queries sparingly. Use JOINs instead.
You would need at least one query (using JOINs) for:
AUTHOR
CUSTOMER
BOOK
...
Then you can test your queries or views independently (very useful if you are having trouble).