retrieve multiple rows in table

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)

retrieve multiple rows in table

Author

Topic

laetitas
Starting Member

5 Posts

Posted - 2004-09-14 : 12:53:05

Hi
I have two tables, tblPlan with columns plan_id and crea_ts and table tblTime with columns plan_id, crea_ts and val_d.
For each row in tblPlan by plan_id and crea_ts theres corresponding multiple rows in tblTime.

so something like this:
tblPlan
----------
plan_id crea_ts
1 12
1 14
2 20
2 22

tblTime
---------
plan_id crea_ts val_d
1 12 35
1 12 62
1 12 79
1 14 18
1 14 81
2 20 90
2 22 56
2 22 94

How do select all rows from tblTime for each row in tblPlan within a select statement?

Thanks for any help on this.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-14 : 12:56:11

will this do?

select t2.*
from tblPlan t1
inner join tblTime t2 on (t1.plan_id = t2.PlanId)

Go with the flow & have fun! Else fight the flow

laetitas
Starting Member

5 Posts

Posted - 2004-09-14 : 13:09:09

Thanks so much! That did the trick.. I didn't realize it was so simple!!

laetitas
Starting Member

5 Posts

Posted - 2004-09-14 : 14:08:00

Hi
An addition to the above select statement. I want to be able to add a where clause to retrieve only those rows from a list. However I want to be able to generate the list from another table at the same time. This is all contained in a stored procedure.

I know this is wrong but something like this:

select plan_item
into planList
from tblOther;

select t2.*
from tblPlan t1
inner join tblTime t2 on (t1.plan_id = t2.PlanId)
where plan_item in(planList);

Thanks again.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 04:02:36

something like this?

you don't need a temp table.

select t2.*
from tblPlan t1
inner join tblTime t2 on (t1.plan_id = t2.PlanId)
inner join tblOther t1.plan_item = t3.plan_item

you really should take a look at different tipes of joins. this is very basic sql, so you really should be familiar with it...

Go with the flow & have fun! Else fight the flow

laetitas
Starting Member

5 Posts

Posted - 2004-09-15 : 09:10:02

Thanks again for your help! I do know the basics of sql but I always found joins the most difficult to understand...

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 10:08:31

well joins are easy to understan if you draw the tables on a paper and join them on paper. start with simple tables.
worked for me.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources