joining two tables

[fikir]

hello all, I need your help on this

I have two tables

tbl1
col1 col2
1 aa
2 bb
3 cc

and tbl2

col1 col2
1 dd
2 ee

I want to write a simple query so that,
I select records from tbl1 on two conditions

i. if records exist in tbl2 , then their matching records will be selected from tbl1

ii. if no record exists in tbl2, all records from tbl1 will be selected from tbl1 regardless of any thing

I wrote this like

select *
from tbl1
where col1 in (select col1 from tbl2)
or not exists (select col1 from tbl2)

but this is taking a lot of time,
is there an easy and efficient way to write it

Thanks

 

[gmmastros]

select *
from   tbl1
       Left Join tbl1
         On tbl1.col1 = tbl2.col1

You need to be a little careful here. If you add a where clause that uses columns from tbl2, then it will act as an inner join instead. If this happens, let me know and I will explain how to take care of that.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

read about left joins in BOL

"NOTHING is more important in a database than integrity." ESquared

 

[drtree]

Hey,

Sure...

SELECT tbl1.*
FROM tbl1 LEFT OUTER JOIN tbl2
ON tbl1.col1 = tbl2.col1

A "LEFT OUTER JOIN" will get all of the records from tbl1 and only the matching records from tbl2 (it gets all records from the table on the left of the join). You could then put an id field from tbl2 in the query so you know where your matches are.

HTH

Doc Tree

 

[gmmastros]

I made a simple mistake. Sorry.

select *
from   tbl1
       Left Join [!]tbl2[/!]
         On tbl1.col1 = tbl2.col1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fikir]

Thanks george

this is not giving me the right data, it doesn't fullfill my first requirment which is

if tbl2 is not empty,
only records from tbl1 that having matching values in tbl2 will be selected (i.e only those col1 values of tbl1 existing in tbl2 will be picked)

in our case our resultset looks like

col1 col2
1 aa
2 bb

if tbl2 were empty, the resultset would have been


col1 col2
1 aa
2 bb
3 cc

thanks

 

[gmmastros]

Maybe this...

If Exists(Select * From tbl2)
  select *
  from   tbl1
         Inner Join tbl2
           On tbl1.col1 = tbl2.col1
Else
  Select *
  From   tbl1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fikir]

Thank you all