We have 2 tables.

users userID name 1 Имя 1 2 Имя 2 3 Имя 3 user_contact contactID userID FirstField secoundField 1 1 val of Имя 1 Это для Имя 1 2 1 val of Имя 1 Это для Имя 1 3 3 val of Имя 3 Это для Имя 3

The essence of the problem is that you need to pull out more fields, not just FirstField. Such request pulls out only 1.

 SELECT u.*, ( SELECT `FirstField` FROM `user_contact` v WHERE u.`userID` = v.`userID` limit 1 ) as `userID` FROM `users` u;

Result of this query

 userID name FirstField 1 Имя 1 val of Имя 1 2 Имя 2 null 3 Имя 3 val of Имя 3

I need userID name FirstField secoundField 1 Name 1 val of Name 1 This is for Name 1 2 Name 2 null null 3 Name 3 val of Name 3 This is for Name 3

ORDER BY does not channel because it uses a lot of resources.

  • Should I use another 1 SELECT to select the second column? Or are there other methods? - proxnull
  • 2
    you can normally describe the tables - user_contact contactID userID FirstField secoundField 1 1 val of Name 1 This is for Name 1 2 1 val of Name 1 This is for Name 1 3 3 val of Name 3 This is for Name 3 - is this what? - Ale_x
  • Yes, I formatted normally, but when I sent it, it turned out to be :( - proxnull
  • edited val of name 1 - what does that mean? This for Name 1 is what ?? I somehow vaguely imagine what you want to store in the user_contact table. With table users - as everything is clear - just the name of the user and his unique index - right? - Ale_x
  • FirstField = First field secoundField = Second field val of Name 1 is just a value (just called it so that it would be clear that userID matches Name 1) - proxnull

2 answers 2

try this

 SELECT u.*, ( SELECT `FirstField`,`secoundField` FROM `user_contact` v WHERE u.`userID` = v.`userID` limit 1 ) as `userID` FROM `users` u;

In general, the use of subqueries is not ice (sometimes without them is not realistic). Write what should return the query in the form of a table

Purely intuitive guess is your query:

 Select users.*, user_contact.FirstField, user_contact.secoundField FROM users left join user_contact on users.userID=user_contact.userID
  • Here is an intuitive + need to add LIMIT only 1 field. but it displays all the fields from contacts (duplicate) - proxnull
  • Is the 2nd query correct? - Ale_x

I read it several times - I didn't master it, what do you really need? if it does not work out in words, then at least for the sake of example, show the tablets: what you need to get the source Why sorting is needed is not yet clear and why it is so expensive in your case is also not clear. LIMIT - getting exactly N records. Without sorting does not make sense - because the database does not guarantee in which sequence the records will be (the same query can return in different ways). And show in the tables on more understandable values ​​-

 1----1----вася----пупкин 2----1----вася----непупкин

etc.