Hello

Please help in the preparation of the request. there are three tables, two of them in join like this:

SELECT t1.* FROM table1 t1, table2 t2 WHERE t1.id1 = t2.id2 limit 10

you need to add a third table so that for each record was a counter

 SELECT COUNT(*) as mycount FROM table3 t3 WHERE t3.target_id = t1.id

and add a condition on it

 mycount > 2

and sorting

 order by mycount DESC

I did it only through having, but the explain request told me what needs to be redone. I ask for help.

those. make one query from three tables, and on the third query you need to calculate and get the output and the number and sorting by this field and another condition on it

  • Where did you study sql ?! Tell yourself Join, and write horror. FROM table1 t1 Inner Join table2 t2 ON t1.id1 = t2.id Isn't the entry correct? When you program you observe etiquette (indents, comments, adequate variable names, etc. ...)? So here, too, has its own rules. - nikita_sergeevich February
  • and unless listing through a comma is not join om? there is, only the syntax on, using changes to the where construct. for muksula that way. - pibidi
  • > and unless listing through a comma is not join om? This is a Cartesian product of tuples, in which then WHERE leaves the right one. In all SQL languages, similar constructions ... And people knowingly invented these same Join_s_ And for the WHERE clause, there is always something to write. - nikita_sergeevich February
  • That is, you want to say SELECT t1. * FROM table1 t1 INNER JOIN table2 t2 ON t1.id1 = t2.id2 limit 10 is an order of magnitude faster than this: SELECT t1. * FROM table1 t1, table2 t2 WHERE t1.id1 = t2.id2 limit 10? - pibidi
  • one
    @ nikita_sergeevich Recording separated by commas is a brief kind of "inner join" ... There are many DBMSs ... - timka_s

1 answer 1

For example:

 SELECT t1.*, ( SELECT COUNT(*) cnt FROM table3 t3 WHERE t3.target_id = t1.id HAVING cnt > 2 ) mycount FROM table1 t1, table2 t2 WHERE t.id1 = t2.id2 ORDER BY mycount LIMIT 10

Or so:

 SELECT t1.*, t3.count FROM table1 t1, table2 t2, ( SELECT target_id, COUNT(*) mycount FROM table3 GROUP BY target_id HAVING mycount > 2 ) t3 WHERE t1.id1 = t2.id2 and t3.target_id = t.id ORDER BY t3.mycount LIMIT 10