Filtering T-SQL statement based on 2 combined values in a SUB-QUERY

Harreni
Harreni used Ask the Experts™
on
Hi Experts,

I want to filter my query below based on 2 combined values from a sub-query which is (ConsumerID nvarcha(255) PK &  DatasetID int) using Exists OR IN Operators. So, how to do that?

SELECT 
 C.ConsumerID 
, C.ConsumerName
, S.ServiceArabicName
, DSI.ServiceID
, DSI.DatasetID
, DSI.DatasetActivatedToUsedByService 
,DS.DatasetName
, DS.DatasetAvaiableOnGSB
FROM   Datasets_Services_Intersection DSI INNER JOIN
             Datasets DS ON DSI.DatasetID = DS.DatasetID INNER JOIN
             Services S ON DSI.ServiceID = S.ServiceID INNER JOIN
             Consumers C ON S.ConsumerID = C.ConsumerID

	  WHERE C.ConsumerID & DatasetID in
			 (
			 SELECT DSNA.ConsumerID,DSI.DatasetID  
			 FROM DatasetsNEEDSACTIVATION DSNA
			 )

Open in new window



Thanks a lot in advance,
Harreni
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior .Net Consultant
Top Expert 2016
Commented:
something like this:

SELECT 
 C.ConsumerID 
, C.ConsumerName
, S.ServiceArabicName
, DSI.ServiceID
, DSI.DatasetID
, DSI.DatasetActivatedToUsedByService 
,DS.DatasetName
, DS.DatasetAvaiableOnGSB
FROM   Datasets_Services_Intersection DSI INNER JOIN
             Datasets DS ON DSI.DatasetID = DS.DatasetID INNER JOIN
             Services S ON DSI.ServiceID = S.ServiceID INNER JOIN
             Consumers C ON S.ConsumerID = C.ConsumerID

	  WHERE exists 
			 (
			 SELECT *  
			 FROM DatasetsNEEDSACTIVATION DSNA
                         WHERE DSNA.ConsumerID = C.ConsumerID  and DSI.DatasetID = DatasetID
			 )

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Why not JOIN the table with the other, avoiding a subquery?
SELECT 
 C.ConsumerID 
, C.ConsumerName
, S.ServiceArabicName
, DSI.ServiceID
, DSI.DatasetID
, DSI.DatasetActivatedToUsedByService 
,DS.DatasetName
, DS.DatasetAvaiableOnGSB
FROM   Datasets_Services_Intersection DSI 
    INNER JOIN Datasets DS ON DSI.DatasetID = DS.DatasetID 
    INNER JOIN Services S ON DSI.ServiceID = S.ServiceID 
    INNER JOIN Consumers C ON S.ConsumerID = C.ConsumerID
    INNER JOIN DatasetsNEEDSACTIVATION DSNA ON DSNA.ConsumerID=C.ConsumerID 
WHERE DSNA.DatasetID = DSI.DatasetID

Open in new window

Author

Commented:
Many thanks Eric, working fine with me

Author

Commented:
sorry Mr.vitor;  I just see your reply after I post my reply

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial