<div>
You should use table aliases - each alias serves like a separate table: SELECT p1.*, p2.*, r.* FROM pass p1 JOIN reputas r ON p1.Id = r.id JOIN pass p2 ON p2.id = r.idx
</div>

Co-Founder and Chief Architect

Dustin Saunders

<div>
A sample data set and expected result would greatly help here, as reading your question it's difficult to follow. OR &nbsp;maybe.. <pre><code class="code-1 nolinks" id="code-20-43290569-1">select * from pass
left join respuestas ON pass.id = respuestas.id OR pass.id = respuestas.idx 
</code></pre>BUT If it helps go to <a target="_blank" rel="ugc nofollow">images.google.com</a> and search for 'SQL JOIN', and you'll see images with pretty Venn diagrams that shows intersecting circles A and B, and the T-SQL necessary to code for multiple scenarios of joining. Jim
</div>

<div>
I agree with Jim's statement, if you have a sample schema and some data to show the expected output you can get a more complete answer. &nbsp;My interpretation was also what Jim mentioned, however one line stood out to me in your question: <blockquote>right now I am retrieveibg the value individually when required. it consumes a lot of resources</blockquote>Is that because you're doing it in code and making additional calls to the database? &nbsp;Generally you want to perform the filtering at the level its most efficient so that would lead me to believe its not going in one query. &nbsp; You can rather efficiently (vs in code) pull the data you need using things like LEFT JOIN and then on condition selecting from a result using ISNULL() to get one recordset that isn't out of scope horizontally. &nbsp;Then you aren't doing extra conditionals in code to say "if this was null then use this", etc.
</div>

<div>
Thx Main sql is select * from pass left join &nbsp;respuestas on pass.id=respuestas.id Fine. However each record in the resulting recordset has another value idx that points to a different record in pass so I do this for ebery record in the recordset select * from pass where pass.id=respuestas.idx Pretty clear I guess
</div>

<div>
Then without schema or knowing what your desired output is I think Jim's answer is the solution. &nbsp;I'm guessing there's a more complex relationship where id is a constraint but idx can appear multiple times and you also want each user's relationship there in some succinct way, but I can tell you if you provide a sample schema or data and what you want the output to look like you'll get a more complete answer sooner because we're making assumptions on information you have but we don't.
</div>

<div>
Sounds like you need a <a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15" rel="ugc">hierarchic CTE</a>. 
 
E.g. 
 

<pre><code class="code-10 nolinks" id="code-20-43290607-1">WITH Hierarchy AS (
 SELECT AP.*,
 AR.*,
 0 AS LevelNumber,
 '\\' + CAST(AP.id AS VARCHAR(MAX)) AS PathText
 FROM pass AP
 LEFT JOIN respuestas AR ON AP.id = AR.id
 UNION ALL
 SELECT CP.*,
 CR.*,
 P.LevelNumber + 1,
 P.PathText + '\' + CAST(CP.id AS VARCHAR(MAX))
 FROM Hierarchy P
 INNER JOIN pass CP ON CP.id = P.idx
 LEFT JOIN respuestas CR ON AP.id = AR.id
)
 SELECT H.*
 FROM Hierarchy H
 ORDER BY H.PathText;
</code></pre>
You just need to get the column names unique, instead of the asterisk.
</div>

<div>
Tale Pass. 2 records fields id name id 1 name john id 2 name peter Repuestas one record fields id idn1 idn1 idn2 idn2 With select name from pass left join respuestas on pass.id=respuestas.idn1 I retrieve the name in record id of pass. I get John in name field To retrieve idn2 (a different name) from pass. I need another query, since name is not in the recordset so I go one by one select name from pass where id=idn2 i get peter If the generated recordset has 1,000 records (one query). I have go through 1000 queries to retrieve name in pass record idn2. 
</div>

Manager

Roberto Parker

<div>
<div class="content wysiwyg-content fr-view">
I have an ms sql query select * from pass left join respuestas where pass.id=respuestas.id The respuestas record points to pass and I can retrieve any field value from pass or respuestas from the generated recordset. But the respuestas table has another value say idx that ponts to adifferent pass record. I need a value from the pass table.right now I am retrieveibg the value individually when required. it consumes a lot of resources. Is there a way ad add it to the recordset or somethng like select * from pass left join respuestas where pass.id=respuestas.idx but I have to match the records. I am at a loss 
</div>
</div>


I have an ms sql query
select * from pass left join respuestas where pass.id=respuestas.id
The respuestas record points to pass and I can retrieve any field value from pass or respuestas from the generated recordset.
But the respuestas table has another value say idx that ponts to adifferent pass record. I need a value from the pass table.right now I am retrieveibg the value individually when required. it consumes a lot of resources. Is there a way ad add it to the recordset or somethng like
select * from pass left join respuestas where pass.id=respuestas.idx but I have to match the records. I am at a loss

Sql in a join 2 diffrenet value rquires form same table

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.