How to use IN opeartor in a sub query..

Question

How to use IN opeartor in  a sub query..

My query is :

table 1 inner join table 2 on condition 1

full outer join

table 3 inner join table 4 on condition 2 and condition 2

I need to use extra condition here with IN opeartor

Answer 1

One choice is to extract the data into temp table. Then use IN operator to filter out further.

---

Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

Answer 2

Can you explain what exactly do you have in mind?

You can do

;with cte as (your current select statemet)

select * from cte where SomeColumn IN (.....)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

Hi Naomi please see below example ...

Role                   Loan

branch               1

agent                2

branch               3

agent               4

agent               1

I want all branches and agents if agent loan number not in branch loan number .

My expected output :

branch   1

branch   3

agent    2

agent     4            (discarding agent   1 as 1 is branch loan )

Answer 4

Try:

CREATE TABLE #RoleLoan
(
 [Role] VARCHAR(20),
 [Loan] INT
)

INSERT #RoleLoan
SELECT 'Branch', 1 UNION ALL
SELECT 'Agent', 2 UNION ALL
SELECT 'Branch', 3 UNION ALL
SELECT 'Agent', 4 UNION ALL
SELECT 'Agent', 1


SELECT * FROM #RoleLoan 
WHERE Role = 'Branch'
UNION ALL
SELECT * FROM #RoleLoan O
WHERE NOT EXISTS(SELECT TOP 1 1 FROM #RoleLoan WHERE [Role] <> O.[Role] AND Loan = O.Loan)
AND O.[Role] = 'Agent'


DROP TABLE #RoleLoan

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post helpful.

Answer 5

Instead of using WHERE Exists, you can also use OUTER APPLY, like:

CREATE TABLE #RoleLoan
(
 [Role] VARCHAR(20),
 [Loan] INT
)

INSERT #RoleLoan
SELECT 'Branch', 1 UNION ALL
SELECT 'Agent', 2 UNION ALL
SELECT 'Branch', 3 UNION ALL
SELECT 'Agent', 4 UNION ALL
SELECT 'Agent', 1




;WITH CTE
AS
(
 SELECT *
 FROM #RoleLoan O
 OUTER APPLY 
 (SELECT CASE	WHEN O.Role = 'Branch' THEN 0 
				WHEN O.Role = 'Agent' THEN 1 END AS Flag
				 FROM #RoleLoan WHERE O.[Role] <> [Role] AND O.Loan = Loan )X

)


SELECT DISTINCT Role, Loan  
FROM CTE 
WHERE ISNULL(Flag, 10) <> 1



DROP TABLE #RoleLoan

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post helpful.

Answer 6

;with cte as (select Role, Loan, row_number() over (partition by Loan order by case when Role = 'branch' then 1 else 2 end) as Rn
 from Loans)

select Role, Load from cte where Rn = 1 -- if we have both branch and agent for the same loan, only take branch.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog