Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql Query


Sql Query

Author
Message
SqlServerLover
SqlServerLover
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 382
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..
Stuart Davies
Stuart Davies
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4520 Visits: 4558
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
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
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3046 Visits: 5478
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
SqlServerLover
SqlServerLover
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 382
Thanks SSCrazy. Union all does not work in my case as i do not want two rows .........
but left join worked.. thanks.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45442 Visits: 39943
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3046 Visits: 5478
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
SqlServerLover
SqlServerLover
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 382
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search