CODEWITHSUNDEEP
sql-server
camacnei
camacnei

Reputation: 101

How to compare the results of two separate queries that have a common field in Sql Server?

Maybe it's because it's Friday but I can't seem to get this and it feels like it should be really really easy.

I have one result set (pulls the data from multiple tables) that gives me the following result set:

Room Type | ImageID | Date

The next query (pulled from separate tables than above) result give me :

ImageID | Date | Tagged

I just want to compare the results to see which imageid's are common between the two results, and which fall into each list separately.

I have tried insert the results from each into temp tables and do a join on imageid but sql server does NOT like that. Ideally I would like a solution that allows me to do this without creating temp tables.

I researched using union but it seems that because the two results don't have the same columns I avoided that route.

Thanks!

Upvotes: 0

Views: 5952

Answers (4)

M.Ali
M.Ali

Reputation: 69524

select q1.ImageID 
from  (your_first_query) q1
WHERE EXISTS (select 1
              from (your_second_query)
              WHERE ImageID = q1.ImageID)

Upvotes: 0

Patrick
Patrick

Reputation: 5846

SELECT TableA.ImageID
FROM TableA
WHERE TableA.ImageID 
IN (SELECT TableB.ImageID FROM TableB)

Upvotes: 0

SylvainL
SylvainL

Reputation: 3938

You don't explain why SQL-Server does not like performing a join on ImageId. Shouldn't be a problem. As to your first question, you need to transform your two queries into subqueries and perform a Full Out Join on them:

Select * from
(Select Room Type, ImageID, Date ...) T1 Full Outer Join
(Select ImageID, Date, Tagged ...) T2 on T1.ImageId = T2.ImageId

The analysis of Null values on both side of the join should give you what you want.

Upvotes: 0

jpw
jpw

Reputation: 44891

You can do this a number of different ways, for instance you can use either a inner join or intersect using the two sets as derived tables.

select ImageID from (your_first_query)
intersect
select ImageID from (your_second_query)

or 

select query1.ImageID 
from (your_first_query) query1
inner join (your_second_query) query2 on query1.ImageID = query2.ImageID

Upvotes: 1

Related Questions