CODEWITHSUNDEEP
sql
Harold_Finch
Harold_Finch

Reputation: 722

Match Two Select Statements for Comparison

I have two sql views with the following select statements:

1.

 select SUBSTRING(UpdatedColumn,3, 9) as [PartialSearch] from [Database_Name].[dbo].[Table]

2.

select SUBSTRING(OldColumn,3, 9) as [PartialSearchOnLiveTable] from [Same_Database_Name].[dbo].[DifferentTable]

Both these views only return 1 column respectively. Now I want to compare values in the first view against values in the second view and return the UpdatedColumn data where a match is not found.

I have considered using comparison operators but just can't seem to get what I want since according to my logic, I will have to specify the conditional checking of the views against each other in the where clause and sql does not permit that. Also, a UNION/UNION ALL will just give me the result set of all the records. Which is something that I don't want.

Lastly, I had a look at Combine two select statements for date comparison and its not what I'm looking for.

Practically, View 1 returns :

abcxxxxxx
dokjfhxxx
cmodxxxxx
wuysxxxxx
aaallooxx

View 2 Returns:

xdsffsafa
xxxxhjsks
ajdhxxxxx
cmodxxxxx
xxxxxx12s
skskshdki
aaallooxx

From the output we see that there are two matches. That of 

aaallooxx
cmodxxxxx

That's the output I am looking for. Any suggestions?

Upvotes: 4

Views: 1725

Answers (2)

Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3340

You can use intersect, set operator between the two queries. So it prints only the common rows present in the two queries.

select SUBSTRING(OldColumn,3, 9) as [PartialSearchOnLiveTable] from [Same_Database_Name].[dbo].[DifferentTable]
intersect --Set Operation between the two queries
select SUBSTRING(UpdatedColumn,3, 9) as [PartialSearch] from [Database_Name].[dbo].[Table]

Upvotes: 5

beejm
beejm

Reputation: 2481

select SUBSTRING(UpdatedColumn,3, 9) as [PartialSearch] 
from [Database_Name].[dbo].[Table] a 
INNER JOIN [Database_Name].[dbo].[Diff_Table] b 
ON a.SUBSTRING(UpdatedColumn,3, 9) = b.SUBSTRING(OldColumn,3, 9)

You can just join them normally using your substring outputs

Upvotes: 0

Related Questions