Searching for a string in two tables using joins

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Searching for a string in two tables using...

Searching for a string in two tables using joins

Rate Topic

Display Mode

Topic Options

Author

Message

Minnu

Posted Tuesday, December 04, 2012 3:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:03 AM
Points: 119, Visits: 361

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

Stewart "Arturius" Campbell

Posted Tuesday, December 04, 2012 5:27 AM

Hall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:48 AM
Points: 3,125, Visits: 4,311

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

Sean Lange

Posted Tuesday, December 04, 2012 8:01 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 8,590, Visits: 8,229

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

Post #1392511

« Prev Topic | Next Topic »

Permissions