outer join -- losing records from parent table question

4 Aug 2006 7:01 PM

CRT##

I want all values from MasterTable, and the associated SubNames from the
view. If a subname is not assigned, return 'Not Assigned'.

This seems to work:

SELECT
    m.Value,
    ISNULL(v.SubName, 'Not Assigned')
FROM MasterTable m
    LEFT JOIN v_View v
        ON m.Key = v.Key
WHERE (v.Name = 'MyCustonName' OR v.Name IS NULL)

However, if 'MyCustomName' doesn't exist in the tables that make up the
view, (let's say it is 'MyCustomNameS'), I only get those records from the
MasterTable that have not been assigned yet.

What I want is all records from MasterTable no matter what. And only if the
SubName is assigned do I want the Subname. Otherwise I want an unassigned
indicator.

How do I do that?

thx.

4 Aug 2006 7:09 PM

Alexander Kuznetsov

Something like this:

SELECT
    m.Value,
    ISNULL(v.SubName, 'Not Assigned')
FROM MasterTable m
    LEFT OUTER JOIN (select * from v_View v
WHERE (v.Name = 'MyCustonName' OR v.Name IS NULL) ) v
        ON m.Key = v.Key

Google up "Meet the experts: Terry Purcell on coding predicates in
outer" for more explanations.

4 Aug 2006 7:26 PM

CRT##

You rock!. Thanks for the quick response!

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1154718572.547548.236890@p79g2000cwp.googlegroups.com...
> Something like this:
>
> SELECT
>     m.Value,
>     ISNULL(v.SubName, 'Not Assigned')
> FROM MasterTable m
>     LEFT OUTER JOIN (select * from v_View v
> WHERE (v.Name = 'MyCustonName' OR v.Name IS NULL) ) v
>         ON m.Key = v.Key
>
> Google up "Meet the experts: Terry Purcell on coding predicates in
> outer" for more explanations.
>

4 Aug 2006 7:26 PM

Alejandro Mesa

CRT##,

In this case, you can put the logical expression as a join condition.

SELECT
    m.Value,
    ISNULL(v.SubName, 'Not Assigned')
FROM
    MasterTable m
    LEFT JOIN
    v_View v
    ON m.Key = v.Key
    and (v.Name = 'MyCustonName' OR v.Name IS NULL)
go

AMB

Show quote

"CRT##" wrote:

> I want all values from MasterTable, and the associated SubNames from the
> view. If a subname is not assigned, return 'Not Assigned'.
>
> This seems to work:
>
> SELECT
>     m.Value,
>     ISNULL(v.SubName, 'Not Assigned')
> FROM MasterTable m
>     LEFT JOIN v_View v
>         ON m.Key = v.Key
> WHERE (v.Name = 'MyCustonName' OR v.Name IS NULL)
>
> However, if 'MyCustomName' doesn't exist in the tables that make up the
> view, (let's say it is 'MyCustomNameS'), I only get those records from the
> MasterTable that have not been assigned yet.
>
> What I want is all records from MasterTable no matter what. And only if the
> SubName is assigned do I want the Subname. Otherwise I want an unassigned
> indicator.
>
> How do I do that?
>
> thx.
>
>
>
>

4 Aug 2006 7:45 PM

CRT##

Thanks!

Do you know what the performance difference is between this and the other
one?

I appreciate your help!

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:BB2A63D3-689A-472C-BF8A-A677B5830B89@microsoft.com...
> CRT##,
>
> In this case, you can put the logical expression as a join condition.
>
> SELECT
>     m.Value,
>     ISNULL(v.SubName, 'Not Assigned')
> FROM
>     MasterTable m
>     LEFT JOIN
>     v_View v
>     ON m.Key = v.Key
>     and (v.Name = 'MyCustonName' OR v.Name IS NULL)
> go
>
>
> AMB
>
>
> "CRT##" wrote:
>
> > I want all values from MasterTable, and the associated SubNames from the
> > view. If a subname is not assigned, return 'Not Assigned'.
> >
> > This seems to work:
> >
> > SELECT
> >     m.Value,
> >     ISNULL(v.SubName, 'Not Assigned')
> > FROM MasterTable m
> >     LEFT JOIN v_View v
> >         ON m.Key = v.Key
> > WHERE (v.Name = 'MyCustonName' OR v.Name IS NULL)
> >
> > However, if 'MyCustomName' doesn't exist in the tables that make up the
> > view, (let's say it is 'MyCustomNameS'), I only get those records from
the
> > MasterTable that have not been assigned yet.
> >
> > What I want is all records from MasterTable no matter what. And only if
the
> > SubName is assigned do I want the Subname. Otherwise I want an
unassigned
> > indicator.
> >
> > How do I do that?
> >
> > thx.
> >
> >
> >
> >

5 Aug 2006 10:53 PM

Erland Sommarskog

CRT## (-) writes:

> Do you know what the performance difference is between this and the other
> one?

The other one, the one that Alexander posted?

Well, try them!

It's often very difficult to tell from a distance which of two similar
queries that will be the fastested, because it depends on a lot of
factors. In this case, I would expect no difference at all, because I
would expect the query plan be more or less the same.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx