What is better practice - 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

General SQL Server Forums

New to SQL Server Programming

What is better practice

Author

Topic

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-05-07 : 17:44:18

(Terms)
TermID, Term
1----- Abc
2----- Arcico
3----- Tunic

and
(RelatedTerms)
TermID, RelatedTermID
1 ------ 3
1------ 2
2------ 4

I want to get the following results

1.- a list of all the terms that start with A%
2.- a list of all the related terms … that belong to terms that start with A%

For number 1 - I am doing a select on Terms table with where term like A%.

For number 2 – I am joining both tables and then once again doing a where term like A%.

Would it be more efficient to take the first results and put them in a table variable, and then just do a join with the second table RelatedTerms.TermID = Terms .TermID

The number of records that generally comeback are between 500 to 1000 records that

What would you consider is a better approach ? or maybe there is an even better way ?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-07 : 18:05:20

join the two tables and use a WHERE table1.term like 'A%'.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

svicky9
Posting Yak Master

232 Posts

Posted - 2007-05-07 : 18:05:45

Why to make it complex
Use a join it will make life easier

1.
select * from terms where term like 'A%'

2.

select
b.termid
from
terms a
join
relatedterms b
on
a.termid = b.termid
where
a.term like 'A%'

Vic

Kristen
Test

22859 Posts

Posted - 2007-05-08 : 01:45:34

"Would it be more efficient to take the first results and put them in a table variable, and then just do a join with the second table RelatedTerms"

No!

"I am joining both tables and then once again doing a where term like A%."

That is the best method

Kristen

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-05-08 : 09:59:32

:) thank you everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources