Good evening. In general, I had to deal with such a thing. I have 2 tables. In the first (table1) information about all users. Example:

user_id:3, name:Вася user_id:5, name:Иван user_id:6, name:Оля

In the second (table2) - information about the interactions between these users. Example:

 user_id:3, his_best_friend_id:5, his_girlfriend_id:6

And you need this: Pull out the names of Vasya's best friend and girl.

As I see it: we take out the IDs of his best friend and girl from the first table. Then, knowing the ID, we do one more query and pull out the name of both. It looks like this:

 $result=mysql_query("SELECT best_friend,girlfriend FROM table2 WHERE user_id='3'"); $row = mysql_fetch_assoc($result) //тут у нас оказываются айдишники 5 и 6 $friend=mysql_query("SELECT name FROM table1 WHERE user_id='".$row['best_friend']."'"); $his_friend=mysql_fetch_assoc($friend); //получаем Иван $girl_friend=mysql_query("SELECT name FROM table1 WHERE user_id='".$row['girlfriend']."'"); //получаем Оля $his_girl_friend=mysql_fetch_assoc($friend);

So there would be no problems, but I get a lot of such Vasya out and it all goes through

 while($row = mysql_fetch_assoc($result)) {}

I am 99% sure that this is not the most optimal solution for such a case))
Maybe someone knows a better option? I will be glad to any links and instructions.

  • Do you have a unique Vasya? - FLK
  • , sorry, corrected Vasya is not unique, user_id is unique - Denis Masster
  • I picked up the values ​​for an example .. and apparently not the most successful, because contrary to logic, but for a better understanding of the picture I will add that the user writes a new best friend and a new girl every day to the base. And then you need to pull out a list of the names of these different friends and girlfriends Vasya for the last 20 days. - Denis Masster
  • If you believe the structure that you described, the query of the form SELECT name FROM table2 WHERE user_id is not correct. - Nord001

2 answers 2

 SELECT n.name, g.name as girl, f.name as friend FROM table1 n join table2 t on t.user_id=n.user_id and n.name='Вася' join table1 g on t.his_girlfriend_id=g.user_id join table1 f on t.his_best_friend_id=f.user_id

upd

if you need the name 'Vasya'

 SELECT n.name, g.name as girl, f.name as friend FROM table2 t join table1 g on t.his_girlfriend_id=g.user_id join table1 f on t.his_best_friend_id=f.user_id join table1 n on t.user_id=n.user_id where t.user_id=3

if the name is not needed:

 SELECT g.name as girl, f.name as friend FROM table2 t join table1 g on t.his_girlfriend_id=g.user_id join table1 f on t.his_best_friend_id=f.user_id where t.user_id=3

upd:

@ Nord001 to my question the comments have run out, there is an index on user_id, so everything will be fine with speed.

  • And the search for a string, not a unique field is not evil? :) - Nord001
  • in principle, in this query, it is absolutely still uniqueness, it will return the correct result. but such are the conditions of the problem, apparently in the real problem this is taken into account. or search will be carried out by user_id - FLK
  • yes, yes, I corrected, I was wrong at the beginning) by user_id the search is on - Denis Masster
  • then everything will be simplified a bit, now I will correct it - FLK
  • and how does this code deal with pulling out many people? see the first comment under the answer Nord001, please. - Denis Masster
 SELECT (SELECT f.name FROM table1 as f WHERE f.id = table2.his_best_friend_id) as friend, (SELECT g.name FROM table1 as g WHERE g.id = table2.his_girlfriend_id) as girlfriend FROM table2 WHERE table2.user_id='3'
  • those. essentially the same 3 requests. as I said above, the example is not the most successful .. but imagine that every day Vasya writes a new best friend and girlfriend to the database. And you need to pull out a list of 20 names Vasina best friends and girlfriends in the last 20 days. Is it necessary to do these 3 requests for each day? Can't you just pull out all 20 friend friend names as a friend and 20 girlfriend as girlfriend? - Denis Masster
  • nested select evil ... - FLK
  • Well, this example will pull out all the girlfriends and friends. and also, as they wrote above about the "evil nested selects" - to be honest, I am not sure about this but I do not refute it, I did not check it myself. - Nord001
  • thank) . - Denis Masster