Regarding derived tables - 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

SQL Server Development (2000)

Regarding derived tables

Author

Topic

sathishkumar
Starting Member

21 Posts

Posted - 2001-12-15 : 04:17:17

I have a table1 with name, startdate, enddate , i need to select data from table2 based table1 conditions. table1 will have more then one records. i don't want to use cursor or while loop. can i use table1 as derived table. but the thing each record in derived table as to be fetched and based on that records to be selcted. i need solutions ASAP

with regards
sathish

Nazim
A custom title

1408 Posts

Posted - 2001-12-15 : 05:09:43

you can do this , but can u be specific with ur problem.

Anywayz something like this should help u out.

select name,startdate,enddate from table1
inner join (select * from database.owner.table2) t
on <givecondition for joining>
where <more filters>

or

select * from table1 where name1 in (select name1 from database.owner.table2 )

there are lot of more ways u can do it. hope it gives u some insight on how to do it.

-------------------------
"Success is when Preparedness meets Opportunity"

kmarshba
Starting Member

24 Posts

Posted - 2001-12-16 : 00:17:38

How is table1 related to table2? From the description of table1 it sounds like their might NOT be a direct relationship to table2.

Nazim's recommendation still stands but you may need to implement a CASE statement in the JOIN to make it work:

SELECT * FROM table1 t1
JOIN table2 t2
ON CASE t1.Name
WHEN 'Accounting' THEN 2
WHEN 'Marketing' THEN 5
ELSE 0 END=t2.Dept_ID

I'm guessing you may need to be a bit more abstract in developing that relationship because it is probably less direct than what I wrote...I don't feel too creative right now.

Give us the table structure for both tables and explain more of what you're trying to do and we can get you a solution.

HTH

sona
Yak Posting Veteran

68 Posts

Posted - 2001-12-17 : 03:58:41

thanx guys,
this join concept working fine ,i formed 4 layer of comparision (output of each join with other). Working fine

sathish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources