Tell me, please, how to make a request. I need from 2 tables to choose the same values ​​of the fields userid and fuserid .

 SELECT a.userid, b.fuserid FROM users, fusers WHERE a.userid=457281 AND b.fuserid=457281

gives empty fields [userid][fuserid] , although there are 2 values ​​in users , in the fuserid table fuserid are no values ​​yet

 SELECT a.userid, b.fuserid FROM users RIGHT JOIN fusers ON a.userid=457281 AND b.fuserid=457281

also gives empty fields [userid][fuserid]

 SELECT a.userid, b.fuserid FROM users LEFT JOIN fusers ON a.userid=457281 AND b.fuserid=457281

gives an unnecessary field, for example:

 [userid][fuserid] [457281][NULL] [457281][NULL] [391419][NULL]

unnecessary field

 SELECT a.userid, b.fuserid FROM users JOIN fusers ON a.userid=457281 AND b.fuserid=457281

gives empty fields

Unification with UNION

 (SELECT a.userid FROM users WHERE a.userid=457281) UNION (SELECT b.fuserid FROM fusers WHERE b.fuserid=457281)

gives one column that has the value 457281, in this case [userid]

I need to select identical records from 2 tables and show them in different columns.
Type:

 [457281][457281] или [NULL][457281] или [457281][NULL]

How can this be achieved?

    6 answers 6

     SELECT max(userid) userid, max(fuserid) fuserid FROM ( SELECT userid as id, userid, NULL as fuserid FROM users WHERE userid=457281 UNION ALL SELECT fuserid , NULL , fuserid FROM fusers WHERE fuserid=457281 ) X GROUP BY id

    If you remove the conditions for the selection of a specific id in the tables, you can get a sample of all the id, which will show which of them are present in which of the tables.

      From the question it is not clear which of the two cases you need.

      If you need to select a value only when it is in both tables, then the easiest way to do this is to use the already mentioned INNER JOIN

       SELECT u.userid, f.fuserid FROM users AS u INNER JOIN fusers AS f ON u.userid = f.fuserid AND f.fuserid = ?

      However, since the ID itself is obviously known to you and you obviously check only the presence of the record, it’s easier to contact the corresponding operators

       SELECT 1 WHERE EXISTS (SELECT * FROM users AS u WHERE u.userid = ?) AND EXISTS (SELECT * FROM fusers AS f WHERE f.fuserid = ?)

      So a query at least in itself will explain its existence.

      In case you need to check if this value is in at least one table, it is enough to make a FULL OUTER JOIN

       SELECT COALESCE(u.userid, f.fuserid) AS result FROM users AS u FULL OUTER JOIN fusers AS f ON u.userid = f.fuserid AND f.fuserid = ?

      Because as is rightly noticed, OUTER JOIN has not yet been delivered to MySQL, it can be roughly emulated via UNION

       SELECT COALESCE(cte.userid, cte.fuserid) AS result FROM ( SELECT u.userid, f.fuserid FROM users AS u LEFT JOIN fusers AS f ON u.userid = f.fuserid AND f.fuserid = ? UNION SELECT u.userid, f.fuserid FROM users AS u RIGHT JOIN fusers AS f ON u.userid = f.fuserid AND f.fuserid = ? )

      In this case, either zero lines or one line with one field will return to you.

      EXISTS still works (focus on the logical operator):

       SELECT 1 WHERE EXISTS (SELECT * FROM users AS u WHERE u.userid = ?) OR EXISTS (SELECT * FROM fusers AS f WHERE f.fuserid = ?)

      If it's critical for you to get a result or null, which, of course, is a little mocking towards SQL, it will suffice to resort to a small hack:

       SELECT COALESCE(u.userid, f.fuserid) AS result FROM users AS u INNER JOIN fusers AS f ON u.userid = f.fuserid AND f.fuserid = ? UNION SELECT null AS result

      You can even force SQL to return exactly one line:

       SELECT * FROM ( SELECT COALESCE(u.userid, f.fuserid) AS result FROM users AS u INNER JOIN fusers AS f ON u.userid = f.fuserid AND f.fuserid = ? UNION SELECT null AS result ) AS cte LIMIT 1

      But all this is the essence of mockery, and I would still look at EXISTS.

      • Your requests are valid, but the result is not the one that is needed, or return empty lines. I need to display entries under a known id in one table, including empty ones. So, there is a table with "questions" and a table with "references". Each of the tables can be empty, and can have values, including the user id. Thus, I need just such a result [457281] [457281] or [NULL] [457281] or [457281] [NULL] i. if there are entries in 2 tables from user id, then this is 1 case, if not, then 2 or 3 cases. - Marat Shaikhutdinov

      It seems something like this:

       SELECT distinct a.userid, b.fuserid FROM users a, fusers b WHERE a.userid = 457281 AND (b.fuserid = 457281 OR b.fuserid is null) OR (a.userid = 457281 OR a.userid is null) AND b.fuserid = 457281
      • SELECT distinct q.questfromuid, f.fndfromuid FROM quest q LEFT JOIN find f ON q.questfromuid = 359837 AND (f.fndfromuid = 359837 OR f.fndfromuid IS NULL) OR (q.questfromuid = 359837 OR q.questfromuid ip) 35 AND f.fndfromuid = 359837 Displays in a form that is necessary, but! besides this, it outputs a different id. How can this be avoided? - Marat Shaikhutdinov
      • RIGHT JOIN prints blank lines if one table is empty - Marat Shaikhutdinov
      • Make an example in sqlfiddle. And in my answer, full join, however, he can not output less than others. - Qwertiy ♦

      As I understand it, only one record is needed for each ID (to see if there are records in any one or both tables).

      Since MySQL does not know about the FULL OUTER JOIN, it seems

       SELECT DISTINCT t1.userid, t2.fuserid FROM ( SELECT userid u FROM users UNION SELECT fuserid FROM fusers ) t0 LEFT JOIN users t1 ON t0.u = t1.userid LEFT JOIN fusers t2 ON t0.u = t2.fuserid -- WHERE t0.u IN (список интересующих ID)

      WHERE can optionally be removed to a subquery - if the list is small and there are many records, this will have an effect.

      If you are strictly interested in one ID, replace the subquery t0 with

       ( SELECT 123456 u ) t0
      • SELECT DISTINCT quest.questfromuid, find.fndfromuid FROM (SELECT quest.questfromuid u FROM quest UNION SELECT find.fndfromuid FROM quest UNION SELECT) t0 LEFT JOIN quest t1 ON t0.u = t1.questfromuid LEFT JOIN find t2 ON t0.u = t fndfromuid WHERE t0.u IN (359837) # 1054 - Unknown column 'quest.questfromuid' in the 'field list' - Marat Shaikhutdinov
      • Handbook SELECT DISTINCT t1.questfromuid, t2.fndfromuid - Akina
       SELECT users.userID, fusers.fuserID FROM users INNER JOIN fusers ON users.userID = 1 and fusers.fuserID = 1
      • Yes, it is a true id, for example, I would like to give you my choice. users.userID = fusers.fuserID WHERE users.userID = 415519 AND fusers.fuserID = 415519. - Marat Shaikhutdinov
      • Changed the code, try using and - user9252920
      • The answer is incorrect. The phrase in the fuserid table is not considered yet there are no values - Akina
      • That's right, Akina. In the userid table, there may also be no YEAR values, as in the fuserid table, that is, the set of values ​​is variable. - Marat Shaikhutdinov

      In all your queries, you forgot to put aliases on the tables. Here is a work request

       SELECT a.userid, b.fuserid FROM users a, fusers b WHERE a.userid=457281 AND b.fuserid=457281

      or as already answered with the addition of conditions

       SELECT u1.id as u1_id, u2.id as u2_id FROM users u1 JOIN fusers u2 ON u1.id = u2.id WHERE u1.id = 457281;
      • The answer is incorrect. The phrase in the fuserid table is not considered yet there are no values - Akina