locked
Sql Query for below requirement RRS feed

  • Question

  • requirement

    table1 table is there in databse1 and database2 
    in that site column is there
    values in site is a,b,c,d,e,f,g,h
    if site value is among a,b,c,d then (select * from database1.table1)
    else if site value is among e,f,g,h then (select * from database2.table1)

    how to write query for this. this query should be written in database3 database
    note: only query not sp or function.

    tsrkreddy

    Monday, May 25, 2015 8:50 AM

Answers

All replies

  • You can write some IF ..ELSE block

    IF @par IN ('a','b','c','d','e','f','g,'h')

         SELECT ......

    ELSE IF .....

        SELECT......


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Eric__Zhang Tuesday, June 2, 2015 1:43 AM
    Monday, May 25, 2015 8:56 AM
    Answerer
  • Hi sivaramasqlserver,

    Regarding your description, the table1 in different databases seem to have the same structure. To somewhat achieve your requirement, you can reference the below query.

    DECLARE @siteValue CHAR(1)
    
    ;WITH Cte AS
    (
    SELECT 'abcd' idCol,* FROM DataBase1.schemaName.Table1
    UNION ALL
    SELECT 'efgh',* FROM DataBase2.schemaName.Table2
    )
    SELECT * FROM Cte WHERE CHARINDEX(@siteValue,idCol)<>0

    If you have any question, feel free to let me know.

    Eric Zhang
    TechNet Community Support



    Tuesday, May 26, 2015 8:27 AM
  • Hi Eric

    But using such approach we are preventing for a query optimizer using an index on that column if one exists


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, May 26, 2015 8:58 AM
    Answerer
  • Hi Uri,

    Thanks for this reminder.

    I don't see any search condition in OP's description, so I just thought a index scan is roughly equivalent to a table scan in this case. Please correct me if I have the wrong idea. :)

    Thanks.

    Eric Zhang
    TechNet Community Support


    Tuesday, May 26, 2015 9:26 AM
  • hey here i need to use select query only for our report purpose and should not be taken any variables

    tsrkreddy

    Wednesday, June 3, 2015 11:51 AM
  • hey here i need to use select query only for our report purpose and should not be taken any variables

    tsrkreddy

    then you need to do like this

    select * from database1.table1
    where site in ('a,b,c,d')
    union all
    select * from database2.table1
    where site in ('e,f,g,h')


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, June 3, 2015 11:59 AM