SQL Server: Solve this problem.

Question

5.00/5 (1 vote)

See more:

Hi Everybody,
Good morning You all.
Domain: SQL Server
Here,There are 2 tables.
1 table name is: t1
2 table name is: t2

SQL

t1

col_1   col_2
1        2
null     10
3        4
89       null
5        6


t2

col_1    col_2
3        2
9        null
5        4
7        6
null     87



o/p table is
 
col_1     col_2
2          3
10         null
4          5
89         null
6          7

I need query result for o/p table..
Pls solve this problem..

This is one interview question so far i haven't find answer...

Thanks&Regards
Karthick

Posted 26-Mar-13 18:30pm

Karruksen

Updated 26-Mar-13 18:33pm

Shanalal Kasim

v3

Add a Solution

Comments

Karruksen 27-Mar-13 0:31am

gvprabu sir pls solve that problem...

_Maxxx_ 27-Mar-13 0:48am

Are you sure you have the 89-null rows correct ? (i.e. are you sure col1 isn't null and col2 89?)

Karruksen 27-Mar-13 0:53am

sure..One of the interview question sir..

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shanalal Kasim · Accepted Answer · 2013-03-26T19:40:00

Hi,

Use below sql

SQL

DECLARE @t1 TABLE(col1 INT,  col2 INT)
DECLARE @t2 TABLE(col1 INT,  col2 INT)
INSERT INTO @t1(col1,col2)
SELECT 1,2
UNION ALL SELECT null, 10
UNION ALL SELECT 3,4
UNION ALL SELECT 89,null
UNION ALL SELECT 5,6
 

INSERT INTO @t2(col1,col2)
SELECT 3,2
UNION ALL SELECT 9,null
UNION ALL SELECT 5,4
UNION ALL SELECT 7,6
UNION ALL SELECT null,87
 
-- Given Input
SELECT col1,col2 FROM @t1
SELECT col1,col2 FROM @t2

-- Required Output
SELECT CASE   WHEN ISNULL( a.col2,-1) =-1  THEN a.col1
              ELSE a.col2 END ,
        ( SELECT col1 FROM @t2 WHERE col2 = CASE   WHEN ISNULL( a.col2,-1) =-1  THEN a.col1
              ELSE a.col2 END )
FROM @t1 a

Logic:

select col2 if col2 is null then select col1 from t1 then
select col1 from t2 where t2.col2 = t1.selected value

_Maxxx_ · Accepted Answer · 2013-03-26T18:52:00

Solution 1

SQL

Insert into outputtable (col1,col2)

select col2,col1 from t2 where col1 is not null and col2 is not null
union
select col2,col1 from t1 where col1 is null
union
select col1,col2 from t1 where col2 is null

Do I get the job?

(p>s. what a dumb interview question!

Posted 26-Mar-13 18:52pm

_Maxxx_

Comments

Karruksen 27-Mar-13 1:02am

Hi sir, Your answer is correct but order is differ from o/p table.
oue result answer is :

2 3
4 5
6 7
10 NULL
89 NULL
--------------------
o/p table real order:

2 3
10 null
4 5
89 null
6 7

_Maxxx_ 27-Mar-13 1:08am

Sure - and why would you want an sql server table to be sequenced exactly? That's why you have order by clauses!

You could simply write the query as a hard-coded insert row by row - the question is a nonsense really

If you needed the sequence to be the same you would probably have to write each select with an additional constraint - and have a select for each row - which again is nonsense.

Karruksen 27-Mar-13 1:20am

Thank u sir,That's enough for me..i am happy..

Shanalal Kasim 27-Mar-13 1:46am

_Maxxx_ : I think the logic is
select col2 if col2 is null then select col1 from t1 then
select col1 from t2 where t2.col2 = t1.selected value

sql is

select case when ISNULL( a.col2,-1) =-1 then a.col1
else a.col2 end ,
( select col1 from t2 where col2 = case when ISNULL( a.col2,-1) =-1 then a.col1
else a.col2 end )

from t1 a

gvprabu 27-Mar-13 2:35am

He need result in 2 Columns not Single Column Output...
So we can't go for UNION or UNION ALL :-)

_Maxxx_ 27-Mar-13 2:38am

You seem to be mistaken over what UNION does - it does not reduce the output to a single column it joins together collections of rows.

gvprabu 27-Mar-13 2:41am

Pls check the solution 2... :-)

_Maxxx_ 27-Mar-13 2:49am

Why? Not sure what you are getting at? Solution 2 does it without union but you said that 'he need result in 2 columns not single column output' My solution didn't have a single column output - I genuinely don't understand what you are saying"?

gvprabu 27-Mar-13 2:46am

yes... your answer is correct but order is different.... I only misunderstand ur solution. great job 5+ :-)

_Maxxx_ 27-Mar-13 2:50am

Yes - and as I commented to the OP it would be a highly unlikely situation if you genuinely wanted a specific output order in an SQL table