Hi,

I have two tables Table1 and Table2.

I need to join Table2 on Table1_SID to get name for SID Column of Table1.

But I need all rows of Table1.

I can use a Left Outer Join to achieve this.

Now I have a problem. There are rows in table2 and table1 where
Table1_SID and SID have -1 value in it.

Don't want to join these rows, join rows where Table1_SID>0 and need to
get all the rows from Table1.

Hope you guys understand what I need.


Thanks
Kiran

Try:

select
    *
from
    Table1 t1
left join
    Table t2 on t2.Table1_SID = t1.SID
                and t2.Table1_SID > 0

Show quoteHide quote

Tom Moreau wrote:
There is a problem with the above query, it doesn't return me the
records of Table1 with SID=-1.

Can you also explain me why doesn't it return the records of Table1 with
SID=-1 when I have the where condition for table2.


Thanks
Kiran

OK, I think I'm understanding what you want.  How about:

select
    *
from
    Table1 t1
left join
(
    select
        *
    from
        Table
    where
        Table1_SID > 0
) t2 on t2.Table1_SID = t1.SID



Show quoteHide quote

Tom Moreau wrote:
Show quoteHide quotethanks a lot, it worked.

does it has any performance issues.


Thanks
Kiran

Performance would depend on how you have indexed your tables.

Show quoteHide quote

Tom Moreau wrote:
Not that, we usually join tables like

select * from table1 left outer join table2 on table1.SID=table2.table1_SID.

In my case, since my requirement was different, I am using it like this

select
      *
from
     Table1 t1
  left join
  (
      select
         *
      from
          Table
      where
          Table1_SID > 0
  ) t2 on t2.Table1_SID = t1.SID


so is there any performance issue between these two queries


Thanks
Kiran

They are two different queries.  I don't understand your question.

Show quoteHide quote

Tom Moreau wrote:
It's ok Tom, thanks a ton for the help.

Just wanted to know is there any decrease in performance due to the way
I join.

Thanks
Kiran

On Mon, 12 Dec 2005 18:42:01 +0000, Kiran wrote:

Hi Kiran,

Comparing the performance of two queries that produce different results
is irrelevant. Unless you have a boss who doesn't care which question
you answer, as long as you answer it quick <g>. That's why I can't
comment on the relative performance of Tom's query vs your original
query.

I can give a generic answer, though. Using a different way to join while
keeping the meaning (and hence the result) of the query the same will
usually (but not always) not change performance. The optimizer will
consider alternative ways to reach the required result anyway. But in
complicated queries, there are cases where rewriting the query enabled
the optimizer to find a better plan that it couldn't find when executing
the original query.

Best, Hugo

finding missing dates in a sequence
Customer specific price matrix design (Long)
Update Query question.
The command(s) completed successfully. - Can i turn this output off?
preparing monthly report