SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT FROM TWO QUERIES
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Silan
Starting Member

3 Posts

Posted - 09/30/2012 :  11:56:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/30/2012 :  18:37:33  Show Profile  Reply with Quote
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.
Go to Top of Page

Silan
Starting Member

3 Posts

Posted - 10/01/2012 :  08:17:00  Show Profile  Reply with Quote
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

Edited by - Silan on 10/01/2012 08:18:34
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/01/2012 :  08:26:10  Show Profile  Reply with Quote
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.
Go to Top of Page

Silan
Starting Member

3 Posts

Posted - 10/01/2012 :  09:31:25  Show Profile  Reply with Quote
You are Right, to make:
JOIN


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

Edited by - Silan on 10/01/2012 09:34:24
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000