I have 2 tables, table1 and table2 which are linked by id (table1) and b_id (table2).
table1 has keyword data which I look for to grab a relevant result (just a simple %like%) (id, keyword_data).
table2 simple records views the result has got, (id, b_id, b_date)
I want to select the record with the least views by default and limit this to 7 results each time.
I have got the below but I know it doesn't look right!
SELECT * FROM table1 WHERE keyword_data LIKE '%xxxx%' INNER JOIN table2 ON table1.id = table2.b_id WHERE ORDER BY COUNT(table2.id) ASC LIMIT 7;
A corrected version(as per my understanding of logic).
SELECT distinct(A.id),A.keyword_data,(select count(table2.b_id) from table2 where table2.b_id=A.id) as count FROM table1 A INNER JOIN table2 B ON A.id = B.b_id WHERE A.keyword_data LIKE '%test%' order by count asc LIMIT 7