I make a general conclusion while from 2 tables, and it is necessary to add a limit to each table in the output. For example, users = LIMIT 1, podarok = LIMIT 4, etc.? How to do it right? My sql:

SELECT `users`.id AS users_id, `podarok`.you_id AS podarok_id ,`users`.* FROM `users` , `podarok` WHERE `users`.id = '".intval($my_id)."' AND `podarok`.you_id = `users`.id
  • @Mike Threw Off ... - Albert Ushakov
  • Ummm in this version is not clear. you already choose only one user and next to each infa record about gifts, why should he limit, if he is alone. total limit of 4 will give 4 entries with this user and 4 gifts - Mike
  • It is not clear why the users table is here at all - Ipatiev
  • @Mike Maybe I didn’t build the query correctly. In general, the problem is that you need to display one user and everything related to him, gifts (4), subscriptions (6) ... From different tables. - Albert Ushakov
  • one
    A strong load is obtained, for example, if we read a table of users in a cycle and for each of them fulfilled a separate request for gifts. And 5 separate queries to different tables, moreover by a simple condition user_id = N (I think there is an index in the database for this field) - this is fast and this is the norm. A query with the union will do the same job in the database. Overhead costs only on compiling queries. but for a large query, the compilation is slower; the optimizer needs to consider more options. so that the difference in speed if there is something she is miserable - Mike

1 answer 1

To get statistics from different tables on a specific user, you can use the following query:

 SELECT u.*, (SELECT count(id) FROM podarok WHERE user_id=u.id) as podarok_cnt, (SELECT count(id) FROM podpiska WHERE user_id=u.id) as podpiska_cnt FROM user u WHERE u.id=8
  • Can a stupid question. How to display such a request in php? I just didn’t understand how to display such queries within queries ( - Albert Ushakov
  • If you are working through php-pdo, then a good manual is phpfaq.ru/pdo ; In php, you specify the request as it is, no matter how many sub-requests it contains. In my example, the result of each subquery is added to its own column. - Sequent
  • Well, I understand, you can just throw off the output of php as if you were withdrawing from this request now ... - Albert Ushakov