Subqyery help... - 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 2005 Forums

Transact-SQL (2005)

Subqyery help...

Author

Topic

danielc
Starting Member

49 Posts

Posted - 2009-04-02 : 00:00:44

Hello folks,

I have the following query that I input the results into a temp table and then inner join the temp table to itself to get the results I need. The only reason I do this is to get around from using subqueries. Not that I don't want to, but because I am very weak when it comes to using them. I really want to learn how to use subqueries but for some reason I can't get my head around them. The query is as follows:

select stu1.sn, mst.* into #temp
from tch inner join mst on tch.sc = mst.sc and tch.tn = mst.tn
inner join sec on mst.sc = sec.sc and mst.se = sec.se
inner join (select * from stu where stu.sc = 910 and stu.tg = '' and stu.del = 0)
as stu1 on stu1.sc = sec.sc and stu1.sn = sec.sn
order by stu1.id, mst.pd asc

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

select #temp.*
from #temp inner join (select * from #temp)as #temp1 on #temp.sn = #temp1.sn
where #temp.tn = 22 and #temp1.tn = 28

I execute the second query to get my results... I know there is a better way... Also, if anyone can point me in the right direction to get more help with suqueries...

Thank you,

Daniel

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-02 : 02:11:10

select #temp.*
from #temp inner join #temp as #temp1 on #temp.sn = #temp1.sn
where #temp.tn = 22 and #temp1.tn = 28

i think u will get any rows from this query.........
use OR in place of AND

or try this once also
select * from #temp where tn in (22,28)

danielc
Starting Member

49 Posts

Posted - 2009-04-02 : 02:45:25

Inner joining the temp table is what allows me to see the results I need. If I use IN, OR then my results are incorrect. Too many returned rows. Using both queries returns the correct results but I would like to get away from that convention and not have to use #temp tables. A subquery should work, but I just don't know how.

Thanks,

Daniel

danielc
Starting Member

49 Posts

Posted - 2009-04-02 : 17:50:46

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources