Join and Subquery - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Join and Subquery

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-28 : 08:24:22

Sissi writes "Hi, I have a question:
Can you do a subquery with a join? In other words, can I use a select statement to set the join criteria? I can do it in access, but this is unsuccessful in SQL..How can you do this
in SQL? thanks."

Nazim
A custom title

1408 Posts

Posted - 2002-04-28 : 08:40:32

Are you looking for Derived Query's.

Select a.column1,b.column2 from table1 a
inner join (select column2,key from table2
where column3 between 100 and 200) b
on a.key=b.key

HTH

--------------------------------------------------------------

sissi
Starting Member

20 Posts

Posted - 2002-04-29 : 14:50:19

Thanks Nazim. However when I use the query using SQL Query Analyzer
it doesn't know A or B tables in:
Select a.column1,b.column2 from table1 a
inner join (select column2,key from table2
where column3 between 100 and 200) b
on a.key=b.key

And to be more precise, I would like to use the following query using
SQL ANalyzer:
SELECT Table1.Key1, Table1.Key2, Table2.Key3, Table2.Key4
FROM ([Max(Column)] INNER JOIN Table1 ON [Max(Column)].Key1 = Table1.Key1) INNER JOIN Table2 ON ([Max(Column)].ROUTID = Table2.ROUTID) AND ([Max(Column)].Key1 = Table2.Key1)

***Note: [Max(Column)] would need to be replaced with SELECT TABLE2.Key1, max(TABLE2.ROUTID) AS ROUTID from Table2 GROUP BY TABLE2.Key1

Is it possible to use this query? Thanks for any input.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-04-29 : 15:58:04

Is this the sort of thing you're trying to do?

SELECT Table1.Key1, Table1.Key2, Table2.Key3, Table2.Key4
FROM (SELECT Key1, MAX(ROUTID) AS ROUTID FROM Table2 GROUP BY Key1) AS MaxTable2
INNER JOIN Table1 ON MaxTable2.Key1 = Table1.Key1
INNER JOIN Table2 ON MaxTable2.ROUTID = Table2.ROUTID AND MaxTable2.Key1 = Table2.Key1

sissi
Starting Member

20 Posts

Posted - 2002-04-29 : 16:53:43

Yes this is EXACTLY what I needed. Thanks so much for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources