INNER JOIN BETWEEN TWO SELECTS

[mcerk]

Hi. I have more pretty difficult SQL SELECT statements. But after all they all return 2 fields. [ObjectID] and another field which I'll name [SQL_Field1]. Ok. I'll allso simplify this complicated SQL senteces like this:
SQL1: SELECT ObjectID, SQL_Field1 FROM SomeTable ...
SQL2: SELECT ObjectID, SQL_Field2 FROM SomeOtherTable ...

How can I INNER JOIN this two datasets within same script? I was wandering something like that, but it doesn't work:

(SELECT ObjectID, SQL_Field1 FROM SomeTable ...) AS T1
(SELECT ObjectID, SQL_Field2 FROM SomeOtherTable ...) AS T2
--and now the join I need:
SELECT T1.ObjectID, T1.SQL_Field1, T2.SQL_Field3
FROM T1 INNER JOIN T2
ON T1.ObjectID = T2.ObjectID

So, I'd like a dataset with fields ObjectID, SQL_Field1 and SQL_Field2.


PS: In praxis I wont have only 2 input select sentences but maybe 10, 20... I know that this would work if I'd made 2 views - each for each input SQL SELECT sentece, and then create a join over those wievs. But this is not so conveniant. I'd like to have it all in one script / stored procedure.


tx in advance

Matej

 

[LKBrwnDBA]

Try this:

SELECT * FROM 
(SELECT ObjectID, SQL_Field1 FROM SomeTable ...) T1
INNER JOIN
(SELECT ObjectID, SQL_Field2 FROM SomeOtherTable ...) T2
ON T1.ObjectID = T2.ObjectID

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[PHV]

Why not simply this ?
SELECT T1.ObjectID, T1.SQL_Field1, T2.SQL_Field2
FROM SomeTable AS T1 INNER JOIN SomeOtherTable AS T2 ON T1.ObjectID = T2.ObjectID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886