MS SQL Server New Users - Left outer join

Author

Left outer join

Jim in Arizona

2005-08-23, 8:24 pm

When we create a diagram which defines the relationship between two tables,
there is no way to specify the exact reltionship type, as there is in
Access.

We need to create a relationship that is, in SQL, a left outer join. In
access, this is easy (gives you three radio buttons and you pick one), but
in SQL, we're frustrated.

The end result:

We are creating a ASP.NET page that is trying to call our database. We have
two tables. In our query we are trying to include all of the records from
the parent table even if there are no records that corresespond in the child
table. We need to run several different queries with different critera with
this type of relationship.

I'm not sure if this is on our ASP.NET code side or our SQL command text
side. I believe the ASP.NET code is creating the relationship but I'm still
not completely sure if it has anything to do with the diagram within
enterprise manager between the two tables.

Any help or advice would be appreciated.
TIA,
Jim

Dan Artuso

2005-08-23, 8:24 pm

Hi,
If you are talking about the EM diagrams, you just right click on the join
and you
can specify whatever type you like, including a Full Outer Join.

Dan Artuso

"Jim in Arizona" <tiltowait@hotmail.com> wrote in message
news:uKl3BSCqFHA.3732@TK2MSFTNGP09.phx.gbl...
> When we create a diagram which defines the relationship between two
> tables, there is no way to specify the exact reltionship type, as there is
> in Access.
>
> We need to create a relationship that is, in SQL, a left outer join. In
> access, this is easy (gives you three radio buttons and you pick one), but
> in SQL, we're frustrated.
>
> The end result:
>
> We are creating a ASP.NET page that is trying to call our database. We
> have two tables. In our query we are trying to include all of the records
> from the parent table even if there are no records that corresespond in
> the child table. We need to run several different queries with different
> critera with this type of relationship.
>
> I'm not sure if this is on our ASP.NET code side or our SQL command text
> side. I believe the ASP.NET code is creating the relationship but I'm
> still not completely sure if it has anything to do with the diagram within
> enterprise manager between the two tables.
>
> Any help or advice would be appreciated.
> TIA,
> Jim
>

Jim in Arizona

2005-08-23, 8:24 pm

I got my question answered in the aspnet newsgroup. Bruce had this to say:

"you just select the notes that match the tickets. you don't wnat an outer
join becuase thats what you have now (notes without tickets).

select * from TTickets where Resolve =1

select * from TNotes
where FK in (select PK from TTickets where Resolve =1)

-- bruce (sqlwork.com)"

Thanks for your help,
Jim

"Dan Artuso" <dartuso@ns.com> wrote in message
news:uDN9hcDqFHA.3524@tk2msftngp13.phx.gbl...
> Hi,
> If you are talking about the EM diagrams, you just right click on the join
> and you
> can specify whatever type you like, including a Full Outer Join.
>
> Dan Artuso
>
>
> "Jim in Arizona" <tiltowait@hotmail.com> wrote in message
> news:uKl3BSCqFHA.3732@TK2MSFTNGP09.phx.gbl...
>
>

Sponsored Links