CODEWITHSUNDEEP
phpmysqljoin
Chris M
Chris M

Reputation: 1

PHP / MySQL - Get Results from table1 based on count from table 2

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;

Thanks

Upvotes: 0

Views: 67

Answers (1)

Nimmy Alice Mathew
Nimmy Alice Mathew

Reputation: 95

Errors are

  • ORDER BY COUNT(table2.id) will be treated like 'ORDER BY 8' as COUNT(table2.id) results a numeric value
  • And you have two 'WHERE' clauses.
  • Condition is placed in wrong position in the query.

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

Upvotes: 1

Related Questions