Join Multiple SQL queries

[jrm44]

I am having trouble figuring out how to execut the following SQL query. Say for example I have the following 2 databases.

Db1
Col_1     Col_2
1            a 
2            b
3            c
4            d
5            e
6            f 
7            g
8            j 
9            h
10           i

Db2
Col_1     Col_2
1            j 
2            a
3            d
4            e
5            c
6            b 
7            f
8            g 
9            h
10           i

Then I have the following 2 Select queries:
SELECT Db1.Col_1 AS 'test1' FROM Db1, Db2 WHERE DB2.Col_2=DB1.Col_1 AND DB2.Col_1 < 5

SELECT Db1.Col_1 AS 'test2' FROM Db1, Db2 WHERE DB2.Col_2=DB1.Col_1 AND DB2.Col_1 > 5

I then want the resulting query to be returned in separate columns, to look something along these lines:

test1    test2
a         h
c         i
d
e

Is this possible to do somehow using joins that just cannot figure out? Or is it simpler? Or even more complicated?

Any help is greatly appreciated

[urtrivedi]

DB2.Col_2=DB1.Col_1

you are joining letter to number, it will never return any result

select Db1.Col_1 AS 'test1'

here you selecting col_1 in query and in expected result you showing col_2 ouput, its totally confusing.

Modify your requirement, so tell us just in simple english what u want from db1 and db2

[jrm44]

Sorry, that was just a confusion on my part. My database is very large and I was trying to simplify it. Essentially I want to select values from a column in one database based on a certain set of conditions, and then I want to select values from the same column based on a different set of conditions. And I want to return them in one query in separate columns.

I think my example should have looked like this:

Db1
Col_1     Col_2
1            a 
2            b
3            c
4            d
5            e
6            f 
7            g
8            j 
9            h
10           i

Db2
Col_1     Col_2
5            a 
4            b
3            c
2            d
1            e
10           f 
9            g
8            h 
7            i
6            j

SELECT Db1.Col_1 AS 'test1' FROM Db1, Db2 WHERE DB2.Col_2=DB1.Col_2 AND DB2.Col_1 < 5

SELECT Db1.Col_1 AS 'test2' FROM Db1, Db2 WHERE DB2.Col_2=DB1.Col_2 AND DB2.Col_1 > 5

test1    test2
1         6
2         7
3         8
4         9
          10

[BitBlt]

Unless there is some relationship between the result sets for two queries you mention above, there is no way for SQL to decide how to return data. That's not what SQL is for.

You could do this programmatically without too much trouble. You just have to instantiate two recordsets, then loop through them, reading off one row from each and presenting it, or something like that. It would be agonizingly slow, though.

If you are using SQL2008, you could also use the Row_Number functionality so sort of simulate what you want, like so:

select test1, test2
from 
(
    SELECT Db1.Col_1 AS 'test1', ROW_NUMBER() OVER(ORDER BY Db1.Col_1) AS 'Row Number'
    FROM Db1 
    inner join Db2 
    on DB2.Col_2 = DB1.Col_2 
    where DB2.Col_1 < 5
) as Result1
full join
(
    SELECT Db1.Col_1 AS 'test2', ROW_NUMBER() OVER(ORDER BY Db1.Col_1) AS 'Row Number'
    FROM Db1 
    inner join Db2 
    on DB2.Col_2 = DB1.Col_2 
    where DB2.Col_1 > 5
) as Result2
on Result1.[Row Number] = Result2.[Row Number]

You may wish to play with the joining, and make sure the results you give are accurate for the test data you show above (hint...it doesn't...run your queries against your test data). It's ugly, but it works.

You may even wish to re-evaluate the requirement. Why do they have to be presented this way? Is there some other way to represent the data?

Anyway, best of luck.

[jrm44]

Thank you. I will mess around with what you have there and the join function. I appreciate it.

[urtrivedi]

I assume that you are using mssql 2005 or above

select test1, test2 from 

(SELECT row_number() OVER (ORDER BY Db1.Col_1) AS RowNumber, Db1.Col_1 AS 'test1' FROM Db1, Db2 WHERE DB2.Col_2=DB1.Col_2 AND DB2.Col_1 < 5) a
full outer join 
(SELECT row_number() OVER (ORDER BY Db1.Col_1) AS RowNumber, Db1.Col_1 AS 'test2' FROM Db1, Db2 WHERE DB2.Col_2=DB1.Col_2 AND DB2.Col_1 > 5) b 

on a.rownumber=b.rownumber

[MikeSmithDev]

Kind of an odd query... are you trying to do something like

SELECT
    test1 = CASE WHEN db2.col_1 < 5 THEN db2.col_1 ELSE '' END,
    test2 = CASE WHEN db2.col_1 > 5 THEN db2.col_1 ELSE '' END
FROM db1
INNER JOIN db2
    ON db1.col_2 = db2.col_2

[urtrivedi]

bitbit, I was looking at his tables and meanwhile you posted that query. and we both gave same soultion.

[BitBlt]

@urtrivedi Yep, we sure did! Great minds think alike, right? :-)

@MikeSmithDev No, your query would return something like this (assuming the numerics are stored in an integer datatype):

test1   test2
0       0
4       0
3       0
2       0
1       0
0       10
0       9
0       8
0       7
0       6