Sql Query

Question

Sql Query

SqlServerLover

Ten Centuries

Points: 1348
More actions
March 18, 2013 at 2:02 am

#271782

I have to return one result set by writing two different logic. Both of the logic are very complex.. one logic returns about 5 columns and other logic too returns 5 columns. I am not sure how to combine all columns and display the result in one place as there is not only one key.. there is no fixed key to link both results from temp table... Any idea? If my question is not clear then please let me know.. I will give an example..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Stuart Davies SSCoach Points: 19505 More actions · Answer 1

If you could supply table structure, the queries you have written so far along with sample data - see the first link in my signature for the best way to do this - then I am sure that some one here will be able to help.

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

To combine all records from one or multiple resultsets use UNION [ALL]

To combine all columns from one or multiple resultsets use JOIN (eg. INNER, LEFT/RIGHT OUTER, FULL OUTER)

If you cannot JOIN two resultsets you want to combine (columns wise), use UNION with selecting NULL's in the first resultset for columns from second resultset (it will combine all columns and rows):

SELECT r1.Col1, r1.Col2, ... r1.ColN, NULL, NULL, ... NULL

FROM (SELECT Col1, Col2, ... ColN

FROM ...whatever WHERE ...whatever) r1

UNION ALL

SELECT NULL, NULL, .... NULL, r2.Col1, r2.Col2, ... r2.ColN

FROM (SELECT Col1, Col2, ... ColN

FROM ...another whatever WHERE ...another whatever) r2

...If my question is not clear then please let me know.. I will give an example..

If my answer is not clear (or irrelevant), please provide involved objects DDL, setup of sample data, clear output based on sample data provided and your query in a current state as per article from the link at the bottom of my signature 😉

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SqlServerLover Ten Centuries Points: 1348 More actions · Answer 3

Thanks SSCrazy. Union all does not work in my case as i do not want two rows .........

but left join worked.. thanks.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

SqlServerLover (3/18/2013)
Thanks SSCrazy. Union all does not work in my case as i do not want two rows .........
but left join worked.. thanks.

Since you said there was no fixed key to join the 2 two results, how'd you manage to pull off a Left Join?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 5

Jeff Moden (3/18/2013)
SqlServerLover (3/18/2013)
Thanks SSCrazy. Union all does not work in my case as i do not want two rows .........
but left join worked.. thanks.
Since you said there was no fixed key to join the 2 two results, how'd you manage to pull off a Left Join?

May be he has some "flexible" or "broken" key?

:hehe:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

SqlServerLover Ten Centuries Points: 1348 More actions · Answer 6

There were few fields (strings types); i combine using them .. this is an example ( LeftTable.string1= RightTable.string1 or RightTable.string1 is Null)

and (LeftTable.string2= RightTable.string2 or RightTable.string2 is Null )

and ( LeftTable.string3= RightTable.string3 or RightTable.string3 is Null )

It worked.. data has been verified..

Thanks guys.