[SOLVED] Grouping tables to pull similar field values

[Buchead]

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.

[kickstart]

Hi

 

First thing is probably to union the reviews and routes tables together:-

 

SELECT 'REVIEW' AS commentType, reviewID AS ID, customerID, review AS comment, `date` AS ComDate FROM reviews
UNION
SELECT 'ROUTE' AS commentType, routeID AS ID, customerID, comment AS comment, `date` AS ComDate FROM routes
UNION
SELECT 'MAINT' AS commentType, maintenanceID AS ID, customerID, comment AS comment, `date` AS ComDate FROM routes

 

This should give you them all combined with common names you can refer to.

 

The JOIN that to the customers table:-

 

SELECT a.username, b.commentType, b.ID, b.customerID, b.comment, b.ComDate
FROM customers a
INNER JOIN (SELECT 'REVIEW' AS commentType, reviewID AS ID, customerID, review AS comment, `date` AS ComDate FROM reviews
UNION
SELECT 'ROUTE' AS commentType, routeID AS ID, customerID, comment AS comment, `date` AS ComDate FROM routes
UNION
SELECT 'MAINT' AS commentType, maintenanceID AS ID, customerID, comment AS comment, `date` AS ComDate FROM routes) b
ON a.customberID = b.customerID
WHERE customerID = $SomeCustomerId
ORDER BY b.ComDate

 

Think that should give you the basics of what you want.

 

All the best

 

Keith

[Buchead]

Fantastic. Thanks very much for that.