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.
| Author |
Topic |
|
mksdf
Starting Member
26 Posts |
 Posted - 2014-11-17 : 03:42:18
|
| Hi,I am converting a process at work from access to SQL. A lot of the queries involved are queries with joins on queries. What is the best method for doing this in SQL. At the moment I have written all the queries involved as SELECT blah blah blah INTO blah blah blah. Then dropping all the query results at the end but I feel like this might not be the best way of doing it. I think there is about 3 or 4 layers of queries. For example,SELECT * FROM table1 JOIN query1...query1 = SELECT * FROM table2 join query2...query 2 = SELECT * FROM table3 join query3...and so on.I want it to be as easy to follow as possible. I thought about writing each query inside 1 big query but this quickly becomes very messy and hard to follow. If you need any more information let me know what you need and I'll provide. Any advice would be appreciated.Regards |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-17 : 09:02:54
|
| Standard way:SELECT * FROM Table1 t1JOIN ( SELECT * FROM TABLE2 ) t2 ON t1.key = t2.key |
 |
|
|
mksdf
Starting Member
26 Posts |
Posted - 2014-11-18 : 04:47:10
|
| ok. thanks. I have already tried this method and I said it is too messy. Thanks anyway |
|
|
|
sunder.bugatha
Yak Posting Veteran
66 Posts |
Posted - 2014-11-18 : 05:32:11
|
| You can use Common Table Expressions instead :With CTE ( col1, col2) AS (select col1, col2 from table2)select * from table t1 join CTEon t1.col1 = t2.col1Hema Sunder |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-18 : 08:54:51
|
| Perhaps if you posted the whole Access query that you want to convert it might be easier to analyze. |
|
|
|
|
|
|
access to SQL