Hello all,
I'm having some trouble grouping some data from 3 different tables and grabbing that data based upon a value in another table.
Tables:
`reviews`
reviewID
customerID
review
date
`routes`
routeID
customerID
comment
date
`maintenance`
maintenanceID
customerID
comment
date
`customers`
customerID
forename
surname
username
I need to pull all the comments\review (I didn't create the tables and not been allowed to rename the fields to be either review or comment) and the customer username based by a certain customerID and ordering by the date of comment.
I thought of:
SELECT c.username, com.comment, com.date FROM `customers` AS c LEFT JOIN (SELECT customerID,review AS comment,date FROM `review` INNER JOIN
SELECT customerID,comment,date FROM `routes` INNER JOIN SELECT customerID,comment,date FROM `maintenance`) AS com ON com.customerID=c.customerID
WHERE c.customerID='1' ORDER BY com.date ASC
Clearly I'm doing something obviously wrong however don't know what.
Any pointers would be most appreciated. Thanks.