locked
Selecting data from two different table based on condition. RRS feed

  • Question

  • HI,

    I need to select from two different tables based on condition.

    like if the is 1_8_10 check the 3rd character which is 8 then select data from 1st table but if the id is 1_9_10

    the 3rd character which is 9 then select data from 2nd table .

    I know till using substring select 3rd character from here how to do iam not getting.

    Thanks

    Thursday, March 31, 2016 12:26 PM

Answers

  • Hi,

    you can try this after replacing your table name:

    declare @SQL_CMD nvarchar(max);
    declare @string varchar(100);
    set @string='1_9_10'
    set @string='1_8_10'
    
    
    
    set @sql_cmd=N'
    select * from 
    '
    + case  substring(@string,3,1)
    when '8' then'MyTable1'
    when '9' then'MyTable2'
    else 'MyTable1'
    end
    
    print @SQL_CMD
    exec sp_executesql @SQL_CMD 
    Regards


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    • Marked as answer by knewsql Thursday, March 31, 2016 6:06 PM
    Thursday, March 31, 2016 1:26 PM

All replies

  • Please provide DDL for all of the tables involved, sample data provided as Insert statement and the expected output (that can be in comma delimited list).


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, March 31, 2016 12:56 PM
  • Hi,

    Thank you for the reply.

    A Table ddl is:ID,name,type,orderid,date,Emailstosend 

    1st table: orderid,ordername,date,id

    2nd table: orderid,ordername,date,id

    so when we check table A id

     here i check the id if the 3rd character isis 8 then select data from 1st table but if the id is 1_9_10

    the 3rd character which is 9 then select data from 2nd table  for the null dates in both the tables.Please let me know if  you need some more details .

    Thanks


         

    Thursday, March 31, 2016 1:18 PM
  • What I was hoping to get was a couple create table statements and a few insert statements.  That way I can test my answer.  But GIGO.

    SELECT a.ID,a.name,a.type
      ,a.orderid,a.date,a.Emailstosend 
      , coalesce(tbl1.date, tbl2.date) as ordername
       , coalesce(tbl1.date, tbl2.date) as Tabledate
       , coalesce(tbl1.id, tbl2.id) as Tableid
       , case when tbl1.id is not null then 'tbl1'
          when tbl2.id is not null then 'tbl2' 
      end as sourcetable FROM A
    left outer join tbl1
    on tbl1.orderid = a.orderid
    and substring(a.id,3,1) = '8'
    left outer join tbl2
    on tbl2.orderid = a.orderid
    and substring(a.id,3,1) = '9'
    
    


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Thursday, March 31, 2016 1:26 PM
  • Hi,

    you can try this after replacing your table name:

    declare @SQL_CMD nvarchar(max);
    declare @string varchar(100);
    set @string='1_9_10'
    set @string='1_8_10'
    
    
    
    set @sql_cmd=N'
    select * from 
    '
    + case  substring(@string,3,1)
    when '8' then'MyTable1'
    when '9' then'MyTable2'
    else 'MyTable1'
    end
    
    print @SQL_CMD
    exec sp_executesql @SQL_CMD 
    Regards


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    • Marked as answer by knewsql Thursday, March 31, 2016 6:06 PM
    Thursday, March 31, 2016 1:26 PM
  • Hi ,

    you way of writing the query helped me.

    Thank you for your responce.

    Thanks

    Thursday, March 31, 2016 6:07 PM
  • create table [1stTable] (col int)
    Insert [1stTable] values (88)
    
    
    declare @s varchar(15)='1-9_10'
    
    create table [2ndTable] (col int)
    Insert [2ndTable] values (99)
    
    If substring(@s,3,1) = '8' 
    
    Begin 
    Select * from [1stTable]
    End
    
    If substring(@s,3,1) = '9'  
    Begin 
    Select * from [2ndTable]
    End
    
    
    
    
    
    
    drop table [2ndTable],[1stTable]

    Thursday, March 31, 2016 7:04 PM