sql statement help

[habneh]

I have two table say

tbl1
col1 col2
1 xx
2 yy
3 zz
4 dd

and tbl2
col1 col2
1 rr
3 kk
4 ll

now I want to select record from tbl1, if tbl2 doesn't have any record I will select all records from tbl1 otherwise
I will select those records in tbl1 which exist in tbl2 based on col1 of tbl1

I tried it like this, but I am not satisfied with it

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

how can I do it in shorter and efficient way

 

[SQLDenis]

What?????
Is this what you want

select t1.col1, t1.col2
from tbl1 t1 left outer join tbl2 t2 on t1.col1 = t2.col1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

Is this the same query that your boss wants written in 3 lines?

thread183-1353644

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[habneh]

yes thank you
you answered one of my questions

other question, I have another table tbl3

col1 varchar(100)
xx
yy
zz
dd

can I do like this to select all records from tbl1 if there is no data in tbl3 otherwise select those records in tbl1 which are in tbl3 based on col2 of tbl1

select t1.col1, t1.col2
from tbl1 t1 left outer join tbl3 t3 on t1.col2 = t3.col1

Thanks

 

[SQLDenis]

>>can I do like this to select all records from tbl1 if there is no data in tbl3 otherwise select those records in tbl1 which are in tbl3 based on col2 of tbl1

should just work, since you didn't provide DDL and sample data I have nothing to play with

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[habneh]

sorry folks I am not getting what I wanted

my criteria
1, if no data exists in tbl2 then I will select all the data in tbl1
select * from tbl1
2, but if any data exist in tbl2, I will select data from tbl1 existing in tbl

select * from tbl1 where col1 in (select col1 from tbl2)
records that don't exist in tbl2 will not be selected.

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

works fine but not cool way to write it

Thanks