Selecting records where joining table has most records

[Quantum3k]

Hi,

I have the two tables below...

Table_1
-------
id
user_id

Table_2
-------
id
table_1_id


Table_1 can have many of Table_2 and I need to retrieve one Table_1 record which has the most Table_2 records.
This is for a recommendation engine.

Thanks in advance guys

 

[r937]

SELECT t1.id
     , t1.user_id
     , t2.how_many_rows
  FROM Table_1 AS t1
INNER
  JOIN ( SELECT table_1_id
              , COUNT(*) AS how_many_rows
           FROM Table_2
         GROUP
             BY table_1_id ) AS t2
    ON t2.table_1_id = t1.id
ORDER
    BY t2.how_many_rows DESC LIMIT 1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon