SELECT FROM TWO QUERIES - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT FROM TWO QUERIES

Author

Topic

Silan
Starting Member

3 Posts

Posted - 2012-09-30 : 11:56:05

Hi,
How do I select from two queries each has a complicated "INNER JOIN" with lots of "WHERE"?
If the queries were simple I could have done:
SELECT attr1, attr2 FROM table_of_attr1, table_of_attr2
but each of these SELECT became a stored proc, however, for the user these two should be displayed in one schema.
I can't use "CREATE VIEW" because the user specifies parameters.

I tried to bind these two queries without each one being stored proc, in the following way:
SELECT <list of all attributes needed from all tables needed>
FROM <list of all tables needed>
WHERE EXISTS
<first query clause>
AND EXISTS
<second query clause>

But this is not correct.
Can you advise me how to do this (without linked server) in SQL?
Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-30 : 18:37:33

I didn't quite follow what your desired result is - if you are trying to get the N rows from the first query and the M rows from the second query and put them together so you end up with M+N rows, you can use UNION ALL. It would be

SELECT col1, col2,... FROM YourFirstTablesAndJoins WHERE YourWhereClauses
UNION ALL 
SELECT col1, col2,... FROM YourSecondTablesAndJoins WHERE YourWhereClauses

This would require that both the queries return same number of columns and same (or convertible) data types in each column.

If you are getting X columns from the first query and Y columns from the second query and want to put them together so you have X+Y columns, you can make the two queries in to subqueries and join them.

SELECT a.col1, a.col2, ... b.colA, b.colB,...
FROM
(
	SELECT col1, col2,... FROM YourFirstTablesAndJoins WHERE YourWhereClauses
) a FULL JOIN 
(
	SELECT colA, colb,... FROM YourSecondTablesAndJoins WHERE YourWhereClauses
) b ON yourJoinConditionsHere

You will of course, need to have some rules and columns on which you want to join the results of the two queries.

Silan
Starting Member

3 Posts

Posted - 2012-10-01 : 08:17:00

Thanks for your answer, but no:
I have to put together the schemas of two queries result, not
only records:
(TableXcol1, TableXcol2), (TableYcol1, TableYcol2)=>
(TableXcol1, TableXcol2, TableYcol1, TableYcol2)

If the queries were simple I could use simle Select from two tables, but I can't put the second table after "FROM" , becuase each query is quite complex with inner join and where and parameters...

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-01 : 08:26:10

quote:
If the queries were simple I could use simle Select from two tables, but I can't put the second table after "FROM" , becuase each query is quite complex with inner join and where and parameters...

You can make even complex queries with inner joins and parameters into subqueries or cte's which would yield you a virtual table that can then be joined to. That is what I was trying to demonstrate in my second example.

If you can post a simplified example, some of the experts on the forum may be able to offer better suggestions.

Silan
Starting Member

3 Posts

Posted - 2012-10-01 : 09:31:25

You are Right, to make:
JOIN

These tables have an interacting table to join to, each of them.
Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources