SQL Query (Cross query ?) - 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)

SQL Query (Cross query ?)

Author

Topic

kabal22
Starting Member

8 Posts

Posted - 2005-12-25 : 07:02:24

Hi,

In the same query, I need to select rows wich are also present in another table.
I don't know how to write that, can someone help me ?

Example :
SELECT id, ref, name1 FROM table1 WHERE id = 5 AND (if "ref" exists in "table2" OR "ref" is not null in "table2") ORDER BY name1

Thanks in advance,
Kabal

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-25 : 10:10:13

Sounds like a simple INNER JOIN. Spend you holiday reading through this section of sql server's Books Online:
Creating and Changing Relational Data | Query Fundamentals

This might be it. It depends on the relationship between table1 and table2:


select t1.id
         ,t1.ref
         ,t1.name1
from   Table1 as t1
inner join   Table2 as t2
                  on t2.ref = t1.ref

Be One with the Optimizer
TG

kabal22
Starting Member

8 Posts

Posted - 2005-12-25 : 10:19:51

Hi TG,

Thanks for you reply.
That way I get lots of duplicates in the result (ref isn't unique on table2).
Can I add a "DISTINCT" or something ? Is this the best way to do it ?

Rgds,
Kabal

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-25 : 10:25:53

That's why I said "depending on the relationship between the 2 tables"

see which one of these performs better:


select t1.id
         ,t1.ref
         ,t1.name1
from   Table1 
where t1.ref in (select distinct ref from Table2)



select t1.id
         ,t1.ref
         ,t1.name1
from   Table1 as t1
inner join   (select distinct ref from Table2) as t2
                  on t2.ref = t1.ref

Be One with the Optimizer
TG

kabal22
Starting Member

8 Posts

Posted - 2005-12-25 : 10:33:37

Thanks!

How can I check the performance of those queries ?

Kabal

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-25 : 10:45:08

A simple way would be to run each of them in a query analyzer window and look at the duration in the status bar of window (bottom right).

Its also a good idea to start looking at the execution plan (set show plan text) in connection properties window.
or look at the "estimated query plan" You can read up on that in Books Online. Eventually that can be very helpfull in optimizing queries.

Be One with the Optimizer
TG

kabal22
Starting Member

8 Posts

Posted - 2005-12-25 : 10:57:10

Thanks for all TG!

kabal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources