CODEWITHSUNDEEP
sqlsql-server
user1250264
user1250264

Reputation: 905

Want to get only 1 record back from an inner join that can pass back multiple records

I have the following SQL query:

SELECT * 
FROM My_TABL wr 
INNER JOIN His_TABL pk ON (wr.Company = pk.company AND wr.NUMBER = pk.number)
WHERE wr.NUMBER = 'L00499233' 
  AND wr.S_CODE IN ('in', 'ji', 'je')

I want to get back 1 record but found out that it can pass back multiple records because a record could have more than 1 field with 'in', 'ji' and 'je' How can I just pick the first one? Thanks.

Upvotes: 0

Views: 52

Answers (2)

alan
alan

Reputation: 6933

If the goal is to join to the top 1 match on the join (ultimately returning several rows), use an OUTER APPLY:

SELECT  *
FROM    My_TABL wr
OUTER APPLY ( SELECT TOP 1 * FROM His_TABL pk WHERE wr.Company = pk.company
                            AND wr.NUMBER = pk.number ) AS pk2
WHERE   wr.NUMBER = 'L00499233'
        AND wr.S_CODE IN ( 'in', 'ji', 'je' );

However, if the goal is to return only a single row in your result set, use Stuart's suggestion.

Upvotes: 1

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

If it doesn't matter which row you want, you can use TOP 1:

select TOP 1 * from My_TABL wr 
inner join His_TABL pk on (wr.Company = pk.company and wr.NUMBER = pk.number)
where wr.NUMBER = 'L00499233' and wr.S_CODE in ('in', 'ji', 'je')

Note that you should get out of the habit of using SELECT * and be more precise about the rows and columns you want to retrieve.

Upvotes: 0

Related Questions