Select query from another database using 'exists'

Question

Hello,
I hit an issue to get data using 'IN' operator from another database.
Could you please help me to fix it?
My query looks like this:

SELECT i.EXTERNAL_CODE, tc.SITE_NAME, tr.TRANSACTION_DT

FROM app4_starlims_data.dbo.INVENTORY i
JOIN app4_starlims_data.dbo.VA_TUBE_COLLECTION tc
ON tc.INVENTORYID = i.INVENTORYID
JOIN app4_starlims_data.dbo.INVENTORY_TRANSACTIONS it
ON it.INVENTORYID = i.INVENTORYID
JOIN app4_starlims_data.dbo.transactions tr
ON tr.TRANSACTION_ID = it.TRANSACTION_ID
WHERE i.EXTERNAL_CODE IN
(SELECT vacutainer_ref, quality, [timestamp]
FROM dna_lab_biospecimen.dbo.blood_sample
WHERE quality LIKE '%under%'
AND [timestamp] BETWEEN '02-19-2021' AND '03-07-2021')

Thanks

Answer 1

Hey @Susja ,
The issue is because within IN statement it is exepecting multiple values of a single column and not multiple columns.

SELECT vacutainer_ref, quality, [timestamp]
FROM dna_lab_biospecimen.dbo.blood_sample
WHERE quality LIKE '%under%'
AND [timestamp] BETWEEN '02-19-2021' AND '03-07-2021

So rather than having 2 columns use union statement and seperate columns as
Select c1 from t1
union
Select c2 from t2

Answer 2

Well I just noticed that I was selecting multiple fields. I will try to use only vacutainer_ref which is exact same as i.EXTERNAL_CODE
Hope it should work
Thanks for advice

Answer 3

Thanks for help.
It worked.
My fault was that I didn't realize that subquery returns multiple rows.