November 17, 2014 at 7:09 am
Hi All,
I have 3 different queries with 3 different where conditions ,then i want to get all those 3 columns displayed as a single table output.
Ex
Select col1, col2 from Tb1 where col4=''and col5='' and col6=''
select col14,col5 from tbl3 where col2='f' and col6='4'
select col17,col8 from tbl3 where col1='sd' and col3='6'
now i want a query which can take col1 from q1 and col 5 from q2 and col8 from q3 as a single query is it possible.
as the query 1 2 and 3 i am using contains different criterias with case satatements so only.
November 17, 2014 at 7:25 am
Hi,
Thanks for the Reply, Union All Condition combines the Values , but i need a query
like
select col1,col5,col8 from queries 1 ,2 ,3.
i need to take each column from each of the query
November 17, 2014 at 7:30 am
kriskumark96 (11/17/2014)
Hi,Thanks for the Reply, Union All Condition combines the Values , but i need a query
like
select col1,col5,col8 from queries 1 ,2 ,3.
i need to take each column from each of the query
Are you able to provide some sample DDL, sample data & desired results, as per the link in my signature?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2014 at 7:35 am
Then you need a JOIN.
WITH Query1 AS (
Select col1, col2 from Tb1 where col4=''and col5='' and col6=''
)
, Query2 AS (
select col14,col5 from tbl3 where col2='f' and col6='4'
)
, Query3 AS (
select col17,col8 from tbl3 where col1='sd' and col3='6'
)
SELECT *
FROM Query1
CROSS JOIN Query2
CROSS JOIN Query3
I used a CROSS JOIN because you didn't provide a condition to join on. If such a condition exists, use an INNER JOIN or OUTER JOIN and specify the condition in the ON clause:
WITH Query1 AS (
Select col1, col2 from Tb1 where col4=''and col5='' and col6=''
)
, Query2 AS (
select col14,col5 from tbl3 where col2='f' and col6='4'
)
, Query3 AS (
select col17,col8 from tbl3 where col1='sd' and col3='6'
)
SELECT *
FROM Query1
INNER JOIN Query2 ON col1 = col5 -- whatever appropriate here
CROSS JOIN Query3 ON col14 = col8 -- whatever appropriate here
-- Gianluca Sartori
November 17, 2014 at 7:47 am
kriskumark96 (11/17/2014)
Hi All,I have 3 different queries with 3 different where conditions ,then i want to get all those 3 columns displayed as a single table output.
Ex
Select col1, col2 from Tb1 where col4=''and col5='' and col6=''
select col14,col5 from tbl3 where col2='f' and col6='4'
select col17,col8 from tbl3 where col1='sd' and col3='6'
now i want a query which can take col1 from q1 and col 5 from q2 and col8 from q3 as a single query is it possible.
as the query 1 2 and 3 i am using contains different criterias with case satatements so only.
Quick question, how do these three tables relate to each other (key columns etc.)?
😎
November 17, 2014 at 11:10 pm
Hi,
The three queries is of the same tables with different where condition are used , so that we cannot have a join or cross join on it
Or
Is there any way to add 2 different queries as a single resultant
select col1 from tb1 where col2=''
select col4 from tb1 where col3=''
the resultant should be col1,col4
November 18, 2014 at 12:21 am
kriskumark96 (11/17/2014)
Hi,The three queries is of the same tables with different where condition are used , so that we cannot have a join or cross join on it
Or
Is there any way to add 2 different queries as a single resultant
select col1 from tb1 where col2=''
select col4 from tb1 where col3=''
the resultant should be col1,col4
Maybe you could do something like this:
select col1, col4 = NULL from tb1 where col2=''
union all
select col1 = NULL, col4 from tb1 where col3=''
If that's not what you want, please take the time to provide a worked example showing source data and desired target data, because I am having trouble understanding your requirement.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2014 at 1:57 am
kriskumark96 (11/17/2014)
Hi,The three queries is of the same tables with different where condition are used , so that we cannot have a join or cross join on it
There is nothing that prevents a self-join here, why do you state that you cannot?
Or
Is there any way to add 2 different queries as a single resultant
select col1 from tb1 where col2=''
select col4 from tb1 where col3=''
the resultant should be col1,col4
Without a relationship definition, regardless of the origin of the columns, this does not make much sense. How does col1 relate to col4 in your example? Consider the following example where the TEST_KEY defines the relationship between the two instances of the set.
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.TBL_TEST_SET') IS NOT NULL DROP TABLE dbo.TBL_TEST_SET;
CREATE TABLE dbo.TBL_TEST_SET
(
TESTSET_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
,TEST_KEY INT NOT NULL
,TCOL_01 INT NOT NULL
,TCOL_02 INT NOT NULL
,TCOL_03 INT NOT NULL
,TCOL_04 INT NOT NULL
,TCOL_05 INT NOT NULL
,TCOL_06 INT NOT NULL
,TCOL_07 INT NOT NULL
,TCOL_08 INT NOT NULL
,TCOL_09 INT NOT NULL
,TCOL_10 INT NOT NULL
);
INSERT INTO dbo.TBL_TEST_SET
(
TEST_KEY
,TCOL_01
,TCOL_02
,TCOL_03
,TCOL_04
,TCOL_05
,TCOL_06
,TCOL_07
,TCOL_08
,TCOL_09
,TCOL_10
)
SELECT TOP (10)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) % 3
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 10
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 20
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 30
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 40
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 50
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 60
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 70
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 80
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 90
,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + 100
FROM sys.all_columns;
SELECT
T1.TCOL_05 AS T1_05
,T2.TCOL_08 AS T2_08
FROM dbo.TBL_TEST_SET T1
LEFT OUTER JOIN dbo.TBL_TEST_SET T2
ON T1.TEST_KEY = T2.TEST_KEY
WHERE T1.TCOL_01 = 11
AND T2.TCOL_02 = 24;
November 18, 2014 at 2:53 am
kriskumark96 (11/17/2014)
Hi,The three queries is of the same tables with different where condition are used , so that we cannot have a join or cross join on it
Or
Is there any way to add 2 different queries as a single resultant
select col1 from tb1 where col2=''
select col4 from tb1 where col3=''
the resultant should be col1,col4
Just a guess, if the first select returns
A
B
C
, and the second one returns
1
2
do you need
A 1
B 2
C NULL
?
Then number the results as you need and full-join them by those numberings.
select t1.col1, t2.col4
from (select row_number() over (order by ...) as n, col1
from tb1 where col2='') as t1
full outer join (select row_number() over (order by ...) as n, col4
from tb1 where col3='') as t2 on t1.n=t2.n
you need to choose how the results should be ordered.
November 18, 2014 at 3:19 am
Thanks for the Reply.. It Helped me alot thank you
November 18, 2014 at 3:33 am
serg-52 (11/18/2014)
kriskumark96 (11/17/2014)
Hi,The three queries is of the same tables with different where condition are used , so that we cannot have a join or cross join on it
Or
Is there any way to add 2 different queries as a single resultant
select col1 from tb1 where col2=''
select col4 from tb1 where col3=''
the resultant should be col1,col4
Just a guess, if the first select returns
A
B
C
, and the second one returns
1
2
do you need
A 1
B 2
C NULL
?
Then number the results as you need and full-join them by those numberings.
select t1.col1, t2.col4from (select row_number() over (order by ...) as n, col1
from tb1 where col2='') as t1
full outer join (select row_number() over (order by ...) as n, col4
from tb1 where col3='') as t2 on t1.n=t2.n
you need to choose how the results should be ordered.
Quick thought, this will indeed produce a set which has the desired columns but those values will be paired/matched by the over/order clause in the individual queries, hence the cardinality and the value distribution will control the "implied" relationship. If the over/order clause columns are the same in all query instances, those columns are then effectively the common key for the set and can therefore be used as such!
😎
November 19, 2014 at 4:17 pm
Eirikur Eiriksson (11/18/2014)
serg-52 (11/18/2014)
kriskumark96 (11/17/2014)
Hi,The three queries is of the same tables with different where condition are used , so that we cannot have a join or cross join on it
Or
Is there any way to add 2 different queries as a single resultant
select col1 from tb1 where col2=''
select col4 from tb1 where col3=''
the resultant should be col1,col4
Just a guess, if the first select returns
A
B
C
, and the second one returns
1
2
do you need
A 1
B 2
C NULL
?
Then number the results as you need and full-join them by those numberings.
select t1.col1, t2.col4from (select row_number() over (order by ...) as n, col1
from tb1 where col2='') as t1
full outer join (select row_number() over (order by ...) as n, col4
from tb1 where col3='') as t2 on t1.n=t2.n
you need to choose how the results should be ordered.
Quick thought, this will indeed produce a set which has the desired columns but those values will be paired/matched by the over/order clause in the individual queries, hence the cardinality and the value distribution will control the "implied" relationship. If the over/order clause columns are the same in all query instances, those columns are then effectively the common key for the set and can therefore be used as such!
😎
If data is inserted into the table then that sort order may get mangled and may not serve as the keys anymore. It is better to establish a definitive key relationship between the two sets.
----------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply