Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Searching for a string in two tables using joins Expand / Collapse
Author
Message
Posted Tuesday, December 4, 2012 3:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:15 AM
Points: 233, Visits: 736

Hi Team,


I have two tables,

table 1 : Test1
ColA | Value
----------------------
A 100





Table 2 : Test2
ColA | Value
-----------------------
B 200


if COLA =A is available in Table1 then
COLA : A Value: 100

else
check in Table2, if available
COLA : A Value: 100


Searching for a string in two tables using joins


A : 300
B : 200
c : 300
d : 150


Yes, Using joins..

First it need to check in table1, if value found then assign that values to a string,
if not found then only it should check in table2

for Table1 am using below code
---------------------------------

SELECT RTRIM(
+ 'ColA:'+ISNULL(ColA,'')+', '
+ ISNULL('ColB:'+CAST(ColB AS VARCHAR(10))+' ','')
+ ISNULL('ColC:'+CAST(ColC AS VARCHAR(10))+' ','')
+ ISNULL('ColD:'+CAST(ColD AS VARCHAR(10))+' ','')
)
FROM



for table 2 am using below code:
---------------------------------------

select @query1 = (SELECT
CASE WHEN Col1 IS NULL or Col1 = '' THEN ''
ELSE CASE WHEN Value IS null then
'Col1: ' + rtrim(Col1) +CHAR(10)
ELSE 'Col1: ' +rtrim(usage_rights) + ' (Col2 ' + rtrim(convert(varchar, Value,107)) + ')' +CHAR(10)
END
END +




i want a final code by joining two tables and assign a final string to a variable @query1

Plz Help
Post #1392324
Posted Tuesday, December 4, 2012 5:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 4,121, Visits: 5,488
How about this?
create table #T1 (id char(1), val int)
create table #T2 (id char(1), val int)
insert #T1 values('A',100),('C',300)
insert #T2 values ('B',200),('D',500),('E',400)

SELECT COALESCE(#T1.id, #T2.id) AS ID,
COALESCE(#T1.val, #T2.val) AS Val
FROM #T1
FULL OUTER JOIN #T2 ON #T1.id = #T2.id




____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1392393
Posted Tuesday, December 4, 2012 8:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:10 AM
Points: 13,230, Visits: 12,709
Isn't this the same question you posted yesterday??

http://www.sqlservercentral.com/Forums/Topic1391849-391-1.aspx


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1392511
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse