0

With the below query, I am creating a new table.

select * from TableA, tableB, (another query to make new table)TableC
where condition

This makes my query look long and awful. I don't know if there is a way to make a temporary table to query later.

For example based on the above query:

tableC = another query to make new table
select * from tableA, tableB, tableC
where condition
John Saunders
  • 160,644
  • 26
  • 247
  • 397
hqt
  • 29,632
  • 51
  • 171
  • 250
  • 4
    What DBMS are you using? Common Table Expressions sound like what you are after, but these are not supported in all DBMS. Also, I'd recommend giving [this article](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) a read. Although the ANSI 89 join syntax (`FROM tableA, TableB WHERE..`) is not incorrect, and occasionally has it's place, using Explicit joins is usually easier to read and less prone to error. – GarethD May 29 '13 at 16:58
  • as title, i'm using t-sql, so DBMS is microsoft sql server. thanks for your recommendation :) – hqt May 29 '13 at 17:00
  • If your only concern is that your query looks long and awful, you don't have an actual problem. Even using CTE as suggested by others merely re-arranges the keystrokes without adding any value. – Dan Bracuk May 29 '13 at 17:19
  • 1
    I have edited your title. Please see, "[Should questions include “tags” in their titles?](http://meta.stackexchange.com/questions/19190/)", where the consensus is "no, they should not". – John Saunders May 29 '13 at 17:37
  • It is not really related to the question, but having looked at your queries in pastebin I suspect you have some unwanted cross joins, for instance, is there really no relation between `tblStudent` and `tblReport`? In one of your queries you are creating a [cartesian product](http://en.wikipedia.org/wiki/Cartesian_product) of these two tables. It might be an idea to post your table structure along with your desired result. – GarethD May 29 '13 at 18:35

3 Answers3

4

CTEs are one way to do it

With TableC as 
( SELECT ....
)
SELECT * from tableA, tableB, tableC
WHERE condition
AjV Jsy
  • 5,799
  • 4
  • 34
  • 30
  • `Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.` I meet this error. can you tell me why, please. – hqt May 29 '13 at 17:41
  • http://pastebin.com/Fk0B4VAD here is my query. please take a look at this. thanks :) – hqt May 29 '13 at 17:43
  • 2
    Add a `;` as it says :) ... `use FUH_UNIVERSITY;` - that's the syntax you'll need, but it looks like you will need to specify your table joins properly for the whole thing to work as you expect. – AjV Jsy May 29 '13 at 17:44
1

You have two choices:

  • Using a view which is the simplest case,
  • Using an Indexed View which is a little bit harder, and has pros and cons.
Community
  • 1
  • 1
Amir Pashazadeh
  • 7,170
  • 3
  • 39
  • 69
1

If it was me I would use a temporary table for the purposes of the query.

You could do something like this;

SELECT TA.*, TB.*
INTO #TempTable
FROM TableA AS TA INNER JOIN TableB AS TB ON TA.ID = TB.ID
WHERE ......

Then you can use this table's detail for whatever purpose;

SELECT *
FROM #TempTable

Just remember, it's good practice to DROP it at the end.

DROP TABLE #TempTable

SQL Fiddle

William
  • 6,332
  • 8
  • 38
  • 57
  • can you tell me, when i reuse this table, i receive error : invalid object name #tableC. thanks :) – hqt May 29 '13 at 17:18
  • Are you sure you have replaced my "#TempTable" with your "#TableC" in all three statements? Also the temporary table will only be available in your current query. – William May 29 '13 at 17:20
  • The temp table is only valid for the life of the session or until you drop it. – Chris H May 29 '13 at 17:20
  • http://pastebin.com/PggQ5TLj please watch again for me. i meet that error at line 9 and line 13. thanks :) – hqt May 29 '13 at 17:23
  • See my SQL Fiddle (edit), I'm unsure why it's not working in your example, but I suspect it doesn't like how you join your tables. (See the comment by @GarethD above) – William May 29 '13 at 17:35