Subquery with EXISTS

Transact-SQL
1

I am just wondering how I can use the following query which retrieves more than 8000 rows in the place of the hard coded value in the last line of the below SQL ('primaquine base liquid', 'primaquine base tab')

select * from cv3catalogitemname
where IsForOrder=1 and IsPrimary=0

SELECT
--GET ORDERED AS SYNONYM FROM SUMMARY LINE - THERE DOESN'T SEEM TO BE A BETTER WAY TO DO THIS ...
CASE WHEN Ord.SummaryLine Like '(Ordered as%'
THEN SUBSTRING(Ord.SummaryLine, CHARINDEX('(Ordered as:', Ord.SummaryLine) + 13, CHARINDEX(')', Ord.SummaryLine)-14)
ELSE Ord.Name
END [Ordered Name],
Ord.Name [Catalog Name],
Ord.SummaryLine [Order Summary Line], --included for verification
Ord.IDCode [Order ID],
Ord.RequestedDtm [Order Requested Date],
Usr.DisplayName [Requesting MD],
Usr1.DisplayName [Entering User]
FROM CV3Order Ord
JOIN CV3ClientVisit Visit
ON Ord.ClientVisitGUID = Visit.GUID
AND Ord.ClientGUID = Visit.ClientGUID
AND Ord.ChartGUID = Visit.ChartGUID
JOIN CV3User Usr
ON Ord.CareProviderGUID = Usr.GUID
JOIN CV3User Usr1
ON Ord.UserGUID = Usr1.GUID
WHERE Visit.VisitStatus IN ('DSC')
AND Ord.OrderCatalogMasterItemGUID IN (SELECT GUID FROM CV3OrderCatalogMasterItem WHERE Name IN ('primaquine base liquid', 'primaquine base tab'))

2

What's in cv3catalogitemname that you want to use?

3

I have to get all the names like 'primaquine base liquid' - which are the synonym names - I cant use this query for searching for more than 250 items, by hard coding one by one

4

so something like:

... (your query up to the last Where clause)
where name in(select name from cv3catalogitemname where name like 'primaquine%')

5

I need to get all the names where IsForOrder=1 and IsPrimary=0

6

Got it, Thank you

7

some how it did not give me the correct results

8

post your modified query

1 Like
9

SELECT
--GET ORDERED AS SYNONYM FROM SUMMARY LINE - THERE DOESN'T SEEM TO BE A BETTER WAY TO DO THIS ...
CASE WHEN Ord.SummaryLine Like '(Ordered as%'
THEN SUBSTRING(Ord.SummaryLine, CHARINDEX('(Ordered as:', Ord.SummaryLine) + 13, CHARINDEX(')', Ord.SummaryLine)-14)
ELSE Ord.Name
END [Ordered Name],
Ord.Name [Catalog Name],
Ord.SummaryLine [Order Summary Line], --included for verification
Ord.IDCode [Order ID],
Ord.RequestedDtm [Order Requested Date],
Usr.DisplayName [Requesting MD],
Usr1.DisplayName [Entering User]
FROM CV3Order Ord
JOIN CV3ClientVisit Visit
ON Ord.ClientVisitGUID = Visit.GUID
AND Ord.ClientGUID = Visit.ClientGUID
AND Ord.ChartGUID = Visit.ChartGUID
JOIN CV3User Usr
ON Ord.CareProviderGUID = Usr.GUID
JOIN CV3User Usr1
ON Ord.UserGUID = Usr1.GUID
WHERE Visit.VisitStatus IN ('DSC')
AND Ord.OrderCatalogMasterItemGUID IN (SELECT GUID FROM CV3OrderCatalogMasterItem WHERE Name IN ( select name from cv3catalogitemname where IsForOrder=1 and IsPrimary=0))
order by [Order Requested Date] desc

10

Looks OK

do you have an example of incorrect results?

11

results are empty

12

ok -- so nothing matches. check your data