December 18, 2014 at 4:29 am
Hi Experts!
I have two databases having same tables and structure. I need to combine the select query in one query sql query. Please help me to modify.
DB1
SELECT
I.ITEM_ID as ITEM_ID
, IP_NAME.PROPERTY_STRING AS 'WL'
, IP_ACTIVE.PROPERTY_STRING AS 'ACTIVE'
FROM ITEM I
INNER JOIN DATE_INFO DI
ON DI.DATETIME = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
AND DI.DATE_TYPE = 'D'
LEFT OUTER JOIN ITEM_PROPERTY IP_NAME
ON IP_NAME.ITEM_ID = I.ITEM_ID
AND IP_NAME.START_DATETIME <= DI.DATETIME
AND IP_NAME.END_DATETIME > DI.DATETIME
AND IP_NAME.PROPERTY_TYPE = 'NAME'
WHERE I.ITEM_TYPE='Test'
AND I.START_DATETIME <= DI.DATETIME
AND I.END_DATETIME > DI.DATETIME
DB2
SELECT
I.ITEM_ID as ITEM_ID
, IP_NAME.PROPERTY_STRING AS 'WL'
, IP_ACTIVE.PROPERTY_STRING AS 'ACTIVE'
FROM ITEM I
INNER JOIN DATE_INFO DI
ON DI.DATETIME = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
AND DI.DATE_TYPE = 'D'
LEFT OUTER JOIN ITEM_PROPERTY IP_NAME
ON IP_NAME.ITEM_ID = I.ITEM_ID
AND IP_NAME.START_DATETIME <= DI.DATETIME
AND IP_NAME.END_DATETIME > DI.DATETIME
AND IP_NAME.PROPERTY_TYPE = 'NAME'
WHERE I.ITEM_TYPE='Test'
AND I.START_DATETIME <= DI.DATETIME
AND I.END_DATETIME > DI.DATETIME
December 18, 2014 at 5:06 am
Change the table reference in DB1 to DB1.dbo.ITEM
Change the table reference in DB2 to DB2.dbo.ITEM
and add UNION ALL between the two
Far away is close at hand in the images of elsewhere.
Anon.
December 18, 2014 at 9:12 am
Hi David,
Will you please summarize more how to do that?
December 18, 2014 at 9:21 am
Seriously?
SELECT Column1, Column2, Column3
FROM Table1
WHERE Column1<12 -- another random filter
UNION ALL
SELECT Column4, Column6,Column2
FROM Table2
WHERE Column3 != 2 -- random filter
the columns have to be union-compatible, and there must be the same number of columns in each SELECT that you are UNIONing.
If that doesn't help, look up UNION in Books Online.
December 18, 2014 at 9:27 am
SELECT
I.ITEM_ID as ITEM_ID
, IP_NAME.PROPERTY_STRING AS 'WL'
, IP_ACTIVE.PROPERTY_STRING AS 'ACTIVE'
FROM DB1.dbo.ITEM I
INNER JOIN DATE_INFO DI
ON DI.DATETIME = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
AND DI.DATE_TYPE = 'D'
LEFT OUTER JOIN ITEM_PROPERTY IP_NAME
ON IP_NAME.ITEM_ID = I.ITEM_ID
AND IP_NAME.START_DATETIME <= DI.DATETIME
AND IP_NAME.END_DATETIME > DI.DATETIME
AND IP_NAME.PROPERTY_TYPE = 'NAME'
WHERE I.ITEM_TYPE='Test'
AND I.START_DATETIME <= DI.DATETIME
AND I.END_DATETIME > DI.DATETIME
UNION ALL
SELECT
I.ITEM_ID as ITEM_ID
, IP_NAME.PROPERTY_STRING AS 'WL'
, IP_ACTIVE.PROPERTY_STRING AS 'ACTIVE'
FROM DB2.dbo.ITEM I
INNER JOIN DATE_INFO DI
ON DI.DATETIME = CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
AND DI.DATE_TYPE = 'D'
LEFT OUTER JOIN ITEM_PROPERTY IP_NAME
ON IP_NAME.ITEM_ID = I.ITEM_ID
AND IP_NAME.START_DATETIME <= DI.DATETIME
AND IP_NAME.END_DATETIME > DI.DATETIME
AND IP_NAME.PROPERTY_TYPE = 'NAME'
WHERE I.ITEM_TYPE='Test'
AND I.START_DATETIME <= DI.DATETIME
AND I.END_DATETIME > DI.DATETIME
Far away is close at hand in the images of elsewhere.
Anon.
December 18, 2014 at 12:37 pm
Thank you David it works for me.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply