Union operator in SQL server 2000

45 pts.
Tags:
SQL Query
SQL Server
SQL Server 2000
SQL Server Query
How to give where condition to the result of the query using Union operator?

Software/Hardware used:
SQL server

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could use your current query as an inline view.

Something like this:

SELECT *
FROM
(</b>SELECT ORGANIZATION_ID, INDIVIDUAL_ID, ISNULL(F_NAME, '') + ' ' + ISNULL(M_NAME, '') + ' '
+ ISNULL(L_NAME, '') AS 'Name', ISNULL(ADDRESS_LINE1, '')
+ ' ' + ISNULL(ADDRESS_LINE2, '') + ' ' + ISNULL(ADDRESS_LINE3, '')
+ ' ' + ISNULL(ADDRESS_LINE4, '') AS 'Address', CITY, 'Individual' AS 'Status'--, PAN_NO
FROM dbo.INDIVIDUAL_MASTER
WHERE (ISACTIVE = 'True')
UNION
SELECT busM.ORGANIZATION_ID, busM.BUSINESS_ID, busM.BUSINESS_NAME, ISNULL(busM.ADDRESS_LINE1, '')
+ ' ' + ISNULL(busM.ADDRESS_LINE2, '') + ' ' + ISNULL(busM.ADDRESS_LINE3, '') + ' '
+ ISNULL(busM.ADDRESS_LINE4, '') AS 'Address', busM.CITY, 'Business' AS 'Status'-- busC.PAN_No,
FROM dbo.BUSINESS_MASTER_INFO AS busM--
LEFT OUTER JOIN dbo.Business_Compliance_INfo AS busC
ON busM.BUSINESS_ID = busC.BUSINESS_ID
WHERE (busM.ISACTIVE = 'True')
<b>)
WHERE some_column = 'something';

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Additional details provided by Jyoti1986: -----------------------------
    SELECT ORGANIZATION_ID, INDIVIDUAL_ID, ISNULL(F_NAME, '') + ' ' + ISNULL(M_NAME, '') + ' ' 
    + ISNULL(L_NAME, '') AS 'Name', ISNULL(ADDRESS_LINE1, '') 
    + ' ' + ISNULL(ADDRESS_LINE2, '') + ' ' + ISNULL(ADDRESS_LINE3, '') 
    + ' ' + ISNULL(ADDRESS_LINE4, '') AS 'Address', CITY, 'Individual' AS 'Status'--, PAN_NO
    FROM dbo.INDIVIDUAL_MASTER
    WHERE (ISACTIVE = 'True')
    UNION
    SELECT busM.ORGANIZATION_ID, busM.BUSINESS_ID, busM.BUSINESS_NAME, ISNULL(busM.ADDRESS_LINE1, '') 
    + ' ' + ISNULL(busM.ADDRESS_LINE2, '') + ' ' + ISNULL(busM.ADDRESS_LINE3, '') + ' ' 
    + ISNULL(busM.ADDRESS_LINE4, '') AS 'Address', busM.CITY, 'Business' AS 'Status'-- busC.PAN_No,
    FROM dbo.BUSINESS_MASTER_INFO AS busM-- 
    LEFT OUTER JOIN dbo.Business_Compliance_INfo AS busC 
    ON busM.BUSINESS_ID = busC.BUSINESS_ID
    WHERE (busM.ISACTIVE = 'True')
    how to give where condition to whole query ? -----------------------------
    69,175 pointsBadges:
    report
  • Denny Cherry
    Typically you will want to filter each of the queries separately. In either case you'll want to check the execution plan to ensure that the proper index is being used.
    66,075 pointsBadges:
    report
  • Kccrosser
    Mrdenny's comment about filtering the individual queries can be critical to good performance, especially when combining results with UNIONs. Also, if you are individually filtering the queries AND applying a WHERE clause to the result, consider using UNION ALL instead of UNION. UNION ALL collects the individual query results but does not then try to filter out duplicates, whereas UNION by itself will try to filter duplicates. This can often waste a lot of processing if the result sets are disjoint, or if the duplicates would be eliminated in any case by the subsequent WHERE condition.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following