Sql Query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Sql Query

Sql Query

Rate Topic

Display Mode

Topic Options

Author

Message

SqlServerLover

Posted Monday, March 18, 2013 2:02 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 10, 2013 1:50 PM
Points: 59, Visits: 299

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

Stuart Davies

Posted Monday, March 18, 2013 2:20 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 1:41 AM
Points: 2,477, Visits: 2,140

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

Eugene Elutin

Posted Monday, March 18, 2013 3:02 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 AM
Points: 2,547, Visits: 4,386

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 Wink

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1432108

SqlServerLover

Posted Monday, March 18, 2013 2:52 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 10, 2013 1:50 PM
Points: 59, Visits: 299

Thanks SSCrazy. Union all does not work in my case as i do not want two rows .........
but left join worked.. thanks.

Post #1432349

Jeff Moden

Posted Monday, March 18, 2013 5:49 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910, Visits: 26,800

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1432412

Eugene Elutin

Posted Monday, March 18, 2013 7:40 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 AM
Points: 2,547, Visits: 4,386

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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1432431

SqlServerLover

Posted Tuesday, March 19, 2013 8:59 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 10, 2013 1:50 PM
Points: 59, Visits: 299

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 »

Permissions