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

Sql Query Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 2:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 4:03 PM
Points: 64, Visits: 312
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..
Post #1432097
Posted Monday, March 18, 2013 2:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:59 AM
Points: 3,315, Visits: 3,581
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
Post #1432099
Posted Monday, March 18, 2013 3:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,873, Visits: 5,190
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1432108
Posted Monday, March 18, 2013 2:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 4:03 PM
Points: 64, Visits: 312
Thanks SSCrazy. Union all does not work in my case as i do not want two rows .........
but left join worked.. thanks.
Post #1432349
Posted Monday, March 18, 2013 5:49 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432412
Posted Monday, March 18, 2013 7:40 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:33 AM
Points: 2,873, Visits: 5,190
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?




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

How to post your question to get the best and quick help
Post #1432431
Posted Tuesday, March 19, 2013 8:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 4:03 PM
Points: 64, Visits: 312
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.
Post #1432710
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse