SELECT * FROM users A INNER JOIN (SELECT * FROM [BASE].[dbo].[list]) AS B ON A.idname = B.user_id

Displays remarkably 2 tables.

Purpose: table 1 contains user data (name, year of birth, etc.), and the list table contains only the identifiers of these users, I cannot connect the third table so that the list is from the list and list1 tables, but the data is from users.

Data in the user table:
idname surname name patronymic age
pupkin65 Pupkin Ivan Ivanovich 1965
vasukov63 Vasyukov Peter G. 1963
permskii63 Permsky Denis Gregoryevich 1961
moskva64 Moscow Sergey Ivanovich 1964

list1
user_id
moskva64
vasukov63

list
user_id
pupkin65
permskii63
vasukov63

you need to display the data as in the users table, but only those in the list and list1 table

  • There is no right answer yet. - zaqzaq
  • @ big-vl, give an example of the data and the expected result. “chooses the data sideways” suggests that the correct and will not be with such a question. - Yura Ivanov
  • corrected the question ... - zaqzaq

3 answers 3

The subquery is not needed:

 SELECT A.*, B.* FROM users A INNER JOIN list B ON A.idname = B.user_id

And then just add one more connection with a similar join.

UPD Select:

 SELECT A.* FROM users A INNER JOIN (SELECT user_id FROM list UNION SELECT user_id FROM list1) B ON A.idname = B.user_id

or

 SELECT A.* FROM users A WHERE idname in (SELECT user_id FROM list UNION SELECT user_id from list1)

or

 SELECT A.* FROM users A WHERE idname in (SELECT user_id FROM list) or idname in (SELECT user_id from list1)

or

 SELECT A.* FROM users A WHERE EXISTS (SELECT 1 FROM list WHERE user_id=A.idname) or EXISTS (SELECT 1 from list1 WHERE user_id=A.idname)

the result will be the same (however, you can pick up the data that the result will be different).

  • displays user_id and user_id_1 from the side, and a sheet from user_id is needed - zaqzaq
  • @ big-vl, updated the answer. - Yura Ivanov
  1. Display only the required fields, but not all completely (*)
  2. To select from 3 tables, call the JOIN again.

    If the data is needed only from the users table, it remains only to make the 2nd join and indicate that the data should be taken only from the table with alias A. This is how the query will look like:

     SELECT A.* FROM users as A INNER JOIN [BASE].[dbo].[list] AS B ON (A.idname = B.user_id) INNER JOIN [BASE].[dbo].[list1] AS B1 ON (A.idname = B1.user_id)
    • There are 124 entries in the base list, and 202 entries in the database, but it lists some 19 entries (! The digits for the output are invented), in general, it does not list them. - zaqzaq
    • We are poking our finger into the sky, trying to understand what tables you have there and what are the connections between them. I re-read the question again and I want to understand what is "so that the list is from the list and list1 table"? What is your source table? - Drac5
    • users data is stored there, it selects data sideways, I somehow need all the records from the list and list1 table to get all together in one united user_id column, and then it selects user_id_1 and I do not have such a column in the table - zaqzaq
    • If you have the same column names in several tables, then it will add the postfix _1, _2, _3 to each next identical column. I suggest you update the question and show sample data in all the tables and how you want to output them. And then in the question you say that you need data from users, and in the previous comment you need data from list and list1. - Drac5
    • corrected the question - zaqzaq