question

Susja avatar image
0 Votes"
Susja asked ·

Select query from another database using 'exists'

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


sql-server-general
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The error I've go is:
Lookup Error - SQL Server Database Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

0 Votes 0 ·
NandanHegde-7720 avatar image
1 Vote"
NandanHegde-7720 answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Susja avatar image
0 Votes"
Susja answered ·

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

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hey @Susja ,
Can you please mark it as answer in case if it resolved your issue as it might help other community members as well

0 Votes 0 ·
Susja avatar image
0 Votes"
Susja answered ·

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

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.