join statement question

[habneh]

I have three tables
tbl1 --which is the main table

col1 col2
1 aa
2 bb
3 cc
4 dd

tbl2
col1 col2 (foreign key to tbl1 (col1))
1 2
2 2
3 1
4 3

tbl3
col1 col2 (foreign key to tbl1 (col1))
1 4
2 3
3 3
4 1

I want to write a procedure which have two parameters @par1 and @par2 and selects data from tbl1 one on the following condition

if both @par1 and @par2 are null then all records in tbl1 are selected
or
if @par1 is null and @par2 is not null, all records in tbl1 which exists in tbl2 selected
or
if @par2 is null and @par1 is not null, all records in tbl1 which exists in tbl3 selected

how can I do this in a more efficient single query, my query takes more than a minute for only 2290 records

Thanks,

 

[ptheriault]

Can you post the query you are currently using?

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

 

[habneh]

select * from tbl1
where
(@par1 is null and @apr2 is null)
or
(@par1 is null and @par2 is not null and
col1 in (select col2 from tbl2)
)
or
(@par1 is not null and @par2 is null and
col1 in (select col2 from tbl3)
)

this is my query, bot it is taking 1:02 seconds to extract 2290 records which is awful

Thanks

 

[ptheriault]

How about something like this.

IF @par1 IS NULL and @par2 IS NULL
BEGIN
SELECT *
FROM tbl1
END
ELSE IF @par1 IS NULL and @par2 IS NOT NULL
BEGIN
Select ...
END
ELSE IF @par1 IS NOT NULL and @par2 IS NULL
BEGIN
SELECT...
END

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

 

[monksnake]

how long does it take you to run these queries by themselves?

select col1, col2 from tbl1

select col1, col2 from tbl1 where col1 in (select col2 from tbl2)

select col1, col2 from tbl1 where col1 in (select col2 from tbl3)

<.

 

[habneh]

Thank you paul.

I tried that way before, and my boss doesn't like that.
he always wants things to be done in three lines of code.

it's kinda creepy thinking like that

 

[ptheriault]

Was it faster? If so then show your boss. Explain to him he can have performannce or one select which is hard to read and performs poorly.

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