Count top contributors from two tables?

[sanchez_1960]

I'm really stuck on this query. I have the following customer table:

CREATE TABLE `customers` (
`Customer_ID` int(10) NOT NULL auto_increment,
`Customer_Login` varchar(15) NOT NULL,
`Customer_Password` varchar(150) NOT NULL,
`Customer_Email` varchar(50) NOT NULL,
`Customer_Title` varchar(4) NOT NULL,
`Customer_Firstname` varchar(50) NOT NULL,
`Customer_Lastname` varchar(50) NOT NULL,
`Customer_Company` varchar(50) default NULL,
`Customer_Address1` varchar(50) NOT NULL,
`Customer_Address2` varchar(50) default NULL,
`Customer_City` varchar(50) NOT NULL,
`Customer_County` varchar(50) NOT NULL,
`Customer_Country` varchar(50) NOT NULL,
`Customer_Postcode` varchar(9) NOT NULL,
`Customer_Phone` varchar(20) default NULL,
PRIMARY KEY (`Customer_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


and the following two tables which customers review products and site reviews:


CREATE TABLE `reviews_customers` (
`ReviewC_ID` int(10) NOT NULL auto_increment,
`ReviewC_Date` date NOT NULL,
`ReviewC_Time` time NOT NULL,
`ReviewC_Status` varchar(1) NOT NULL,
`ReviewC_Text` text NOT NULL,
`ReviewC_Rating` int(1) NOT NULL,
`Review_ID` int(10) NOT NULL,
`Customer_ID` int(10) NOT NULL,
PRIMARY KEY (`ReviewC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;


CREATE TABLE `products_customers` (
`ProductC_ID` int(10) NOT NULL auto_increment,
`ProductC_Date` date NOT NULL,
`ProductC_Time` time NOT NULL,
`ProductC_Status` varchar(1) NOT NULL,
`ProductC_Text` text NOT NULL,
`ProductC_Rating` int(1) NOT NULL,
`Product_ID` int(10) NOT NULL,
`Customer_ID` int(10) NOT NULL,
PRIMARY KEY (`ProductC_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

I want to find the top customers from the second two tables and then print the results. I've done a similar thing before but combined in PHP, but I know this is not the way to do it. I want SQL to sort my results so I can just print the results.

[chazzy]

I'm a little lost. What makes something "top"?

[sanchez_1960]

Well, you'll see Customer_ID in the second two tables. I want it to count the results in the second two tables, merge the count and then display the customers table which have the most results of the 2nd two tables.

[chazzy]

So then, something like this maybe?


SELECT
c.*,
r_count,
p_count
from customers c
left join (select count(*) as r_count from reviews_customers rc where c.customer_id = rc.customer_id) a1
left join (select count(*) as p_count from products_customers pc where c.customer_id = pc.customer_id) a2
LIMIT a,b

[mattyblah]

chazzy, i don't think your query is valid sql, though i don't know mysql. it definitely wouldn't be in sql server. Do you know if mysql has the apply operator, used for cross apply or outer apply?

[chazzy]

chazzy, i don't think your query is valid sql, though i don't know mysql. it definitely wouldn't be in sql server. Do you know if mysql has the apply operator, used for cross apply or outer apply?

apply is sql server only. i fail to see how the syntax is not compliant. i know that before 2008 sql server had a lot of issues with subselects, but as far as I am aware it's valid sql, just missing a few values that the OP can fill in himself.