I have these below queries. How do I join these 3 queries into a single query?

1st query:

 SELECT TOP(1) @tmpOnlineCat =  ac.AlertCatID
 FROM   alert.AlertCategory ac
 WHERE  ac.FkAlertID = 2
 AND ac.FkAlertTypeID = 3

2nd query

SELECT TOP(1) @tmpOfflineCat = ac.AlertCatID
      FROM   alert.AlertCategory ac
      WHERE  ac.FkAlertID = 3
      AND ac.FkAlertTypeID = 3

3rd query

INSERT INTO item.ItemOnlineOfflineAlertCategory
VALUES
 (
    @tmpPkItemID,
    @tmpOfflineCat,
    @tmpOnlineCat
    ) 


April 21st, 2015 1:06am

Hi

You can use Case Statement ;

	@tmpOnlineCat =   case WHEN ac.FkAlertID = 2 AND ac.FkAlertTypeID = 3 Then ac.AlertCatID
	@tmpOfflineCat = case WHEN ac.FkAlertID = 3 AND ac.FkAlertTypeID = 3 THEN ac.AlertCatID

Thanks

Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 1:12am

How do I incorporate the insert query as well into this?
April 21st, 2015 1:20am

I think this is all what you need!

INSERT INTO item.ItemOnlineOfflineAlertCategory
SELECT    @tmpPkItemID,
MAX(CASE WHEN FkAlertID = 2 THEN AlertCatID END) AS tmpOnlineCat,
MAX(CASE WHEN FkAlertID = 3 THEN AlertCatID END) AS tmpOfflineCat
FROM alert.AlertCategory
WHERE FkAlertTypeID = 3

Free Windows Admin Tool Kit Click here and download it now
April 21st, 2015 1:31am

You can try this code snippet:

INSERT INTO item.ItemOnlineOfflineAlertCategory
SELECT pkItemID, A.*, B.*
FROM
 (SELECT TOP 1 ac.AlertCatID AS tmpOnlineCat
 FROM alert.AlertCategory ac
 WHERE  ac.FkAlertID = 2 AND ac.FkAlertTypeID = 3 ) A,
 (SELECT TOP 1 ac.AlertCatID AS tmpOfflineCat
 FROM alert.AlertCategory ac WHERE  ac.FkAlertID = 3 AND ac.FkAlertTypeID = 3 ) B

Here you just have to take care of getting pkItemID since your queries did not specify from where are you getting this column

Good Luck !

April 21st, 2015 1:44am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics