Very Computer

Hi everybody!
I'm currently upsizing an Access 97 database to MS SQL Server 7.0.

In Access: I have 2 tables T_1 and T_2 with a LEFT JOIN reinforced
relationship (don't ask me why, I didn't create it).  How is it
translated to SQL Server?

I read about issues about combining LEFT/RIGHT joins and WHERE
statements, but they mention SQL Server 6.5.  Is it still a problem
with v7.0?

Thanks for your help.

FJL

FJL,

Quote:> I read about issues about combining LEFT/RIGHT joins and WHERE
> statements, but they mention SQL Server 6.5.  Is it still a problem
> with v7.0?

To the best of my knowledge there has never been a problem with SQL Server
and the ANSI SQL standard coding of LEFT and RIGHT joins since they were
introduced in, I believe, SQL Server 6.5.

Since, again to the best of my knowledge, there has never been a problem
with them, the answer to your question is: No, there is not a problem with
them in SQL Server 7.0.

However if you would care to elaborate about what the "issues" are, then
perhaps a different answer might be appropriate.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> Hi everybody!
> I'm currently upsizing an Access 97 database to MS SQL Server 7.0.

> In Access: I have 2 tables T_1 and T_2 with a LEFT JOIN reinforced
> relationship (don't ask me why, I didn't create it).  How is it
> translated to SQL Server?

> I read about issues about combining LEFT/RIGHT joins and WHERE
> statements, but they mention SQL Server 6.5.  Is it still a problem
> with v7.0?

> Thanks for your help.

> FJL

Thanks for your help.
I just want to make I understand this well (I don't have much
experience with SQL Server).

In SQL (using whatever tool), you can create a query using LEFT/RIGHT
JOIN as in
"SELECT ... FROM ... LEFT JOIN ON ... WHERE ...".  I have no problem
with that.

In Access, when you build a relationship, you can specify a few
properties such as "Reinforce referential integrity", "cascade
update",...  There's also a feature enabling you to specify the JOIN
type.  On the relational diagram, there's a link between the two
tables, in the form of a straight line.  If you specify a LEFT join,
you can see (still on the relational diagram) an arrow instead of a
straight line.

In SQL Server, I am able to create relationships, but I can't see the
equivalent of the "in-the-relationship-left-join".

Concerning the "issue", here's the original link:
http://groups.google.ca/groups?hl=en&threadm=lxndYBAcBJu1Ewqs%40insci...

FJL

============================================================================
From: BP Margolin
Date Posted: 3/25/2002 1:13:00 PM

FJL,

Quote:> I read about issues about combining LEFT/RIGHT joins and WHERE
> statements, but they mention SQL Server 6.5.  Is it still a problem
> with v7.0?

To the best of my knowledge there has never been a problem with SQL
Server
and the ANSI SQL standard coding of LEFT and RIGHT joins since they
were
introduced in, I believe, SQL Server 6.5.

Since, again to the best of my knowledge, there has never been a
problem
with them, the answer to your question is: No, there is not a problem
with
them in SQL Server 7.0.

However if you would care to elaborate about what the "issues" are,
then
perhaps a different answer might be appropriate.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
can be cut and pasted into Query Analyzer is appreciated.

============================================================================

Thanks for the link. I presume you are referring to the KB article mentioned
in link ... Q124152. That KB article refers to MS Access, not SQL Server.

In an RDBMS, the only relationships that I can think of offhand that
**tables** participate in are PK / FK relationships. SQL Server 2000
supports cascading deletes and cascading updates defined as part of the PK /
FK relationship.

**Queries** however can link tables together in various ways, including
inner joins and outer joins. The relationship of tables in a query, at least
in SQL Server, is defined in the query, and is not part of the definition of
the tables.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> Thanks for your help.
> I just want to make I understand this well (I don't have much
> experience with SQL Server).

> In SQL (using whatever tool), you can create a query using LEFT/RIGHT
> JOIN as in
> "SELECT ... FROM ... LEFT JOIN ON ... WHERE ...".  I have no problem
> with that.

> In Access, when you build a relationship, you can specify a few
> properties such as "Reinforce referential integrity", "cascade
> update",...  There's also a feature enabling you to specify the JOIN
> type.  On the relational diagram, there's a link between the two
> tables, in the form of a straight line.  If you specify a LEFT join,
> you can see (still on the relational diagram) an arrow instead of a
> straight line.

> In SQL Server, I am able to create relationships, but I can't see the
> equivalent of the "in-the-relationship-left-join".

> Concerning the "issue", here's the original link:

http://groups.google.ca/groups?hl=en&threadm=lxndYBAcBJu1Ewqs%40insci....
co.uk&rnum=3&prev=/groups%3Fq%3Dupsizing%2Baccess%2BLEFT%2Bjoin%2Bgroup:comp
.databases.*%26hl%3Den%26selm%3DlxndYBAcBJu1Ewqs%2540insci.demon.co.uk%26rnu
m%3D3

Quote:

> FJL

============================================================================

Quote:> From: BP Margolin
> Date Posted: 3/25/2002 1:13:00 PM

> FJL,

> > I read about issues about combining LEFT/RIGHT joins and WHERE
> > statements, but they mention SQL Server 6.5.  Is it still a problem
> > with v7.0?

> To the best of my knowledge there has never been a problem with SQL
> Server
> and the ANSI SQL standard coding of LEFT and RIGHT joins since they
> were
> introduced in, I believe, SQL Server 6.5.

> Since, again to the best of my knowledge, there has never been a
> problem
> with them, the answer to your question is: No, there is not a problem
> with
> them in SQL Server 7.0.

> However if you would care to elaborate about what the "issues" are,
> then
> perhaps a different answer might be appropriate.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> can be cut and pasted into Query Analyzer is appreciated.

============================================================================

Quote:> > Hi everybody!
> > I'm currently upsizing an Access 97 database to MS SQL Server 7.0.

> > In Access: I have 2 tables T_1 and T_2 with a LEFT JOIN reinforced
> > relationship (don't ask me why, I didn't create it).  How is it
> > translated to SQL Server?

> > I read about issues about combining LEFT/RIGHT joins and WHERE
> > statements, but they mention SQL Server 6.5.  Is it still a problem
> > with v7.0?

> > Thanks for your help.

> > FJL

 The above mentioned is a table property - primary and foreign keys
while JOIN is essential in queries

Thank you for your help.

My understanding is that there are Inner and Outer joins.
A Left inner join and a right inner join are an inner join. This means
that data has to exist in both tables for it to "work".

There is also something called an outerjoin. If you want all the
records in the first table (or left table) even if there is no
corresponding records in the other table (or right table), you would
use a Left Outer Join.

In theory, you can also switch the tables and use a right outer join.
In this case, you would specify the "use all records from this table"
after the words "right outer join", and the other table as the first
table mentioned.

This always gives my little brain a pain, because in theory you can
specify all the permutations with an inner join and a left outer join.

Other thing you can do in Access is right click on the top, and ask to
see the SQL code for it.

I've found that you can do anything in sql server that the access
shows, but for sure there are things in Sql server you can do that
access will not accept.

-Doug Miller

Quote:

> In SQL Server, I am able to create relationships, but I can't see the
> equivalent of the "in-the-relationship-left-join".

> Concerning the "issue", here's the original link:
> http://groups.google.ca/groups?hl=en&threadm=lxndYBAcBJu1Ewqs%40insci...

> FJL

> ============================================================================
> From: BP Margolin
> Date Posted: 3/25/2002 1:13:00 PM

> FJL,

Doug,

Quote:> A Left inner join and a right inner join are an inner join.

I've never heard of a LEFT INNER JOIN nor of a RIGHT INNER JOIN. Would it be
possible for you to provide a code sample of one or the other ?

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:> > Thanks for your help.
> > I just want to make I understand this well (I don't have much
> > experience with SQL Server).

> > In SQL (using whatever tool), you can create a query using LEFT/RIGHT
> > JOIN as in
> > "SELECT ... FROM ... LEFT JOIN ON ... WHERE ...".  I have no problem
> > with that.

> > In Access, when you build a relationship, you can specify a few
> > properties such as "Reinforce referential integrity", "cascade
> > update",...  There's also a feature enabling you to specify the JOIN
> > type.  On the relational diagram, there's a link between the two
> > tables, in the form of a straight line.  If you specify a LEFT join,
> > you can see (still on the relational diagram) an arrow instead of a
> > straight line.

> My understanding is that there are Inner and Outer joins.
> A Left inner join and a right inner join are an inner join. This means
> that data has to exist in both tables for it to "work".

> There is also something called an outerjoin. If you want all the
> records in the first table (or left table) even if there is no
> corresponding records in the other table (or right table), you would
> use a Left Outer Join.

> In theory, you can also switch the tables and use a right outer join.
> In this case, you would specify the "use all records from this table"
> after the words "right outer join", and the other table as the first
> table mentioned.

> This always gives my little brain a pain, because in theory you can
> specify all the permutations with an inner join and a left outer join.

> Other thing you can do in Access is right click on the top, and ask to
> see the SQL code for it.

> I've found that you can do anything in sql server that the access
> shows, but for sure there are things in Sql server you can do that
> access will not accept.

> -Doug Miller

> > In SQL Server, I am able to create relationships, but I can't see the
> > equivalent of the "in-the-relationship-left-join".

> > Concerning the "issue", here's the original link:

http://groups.google.ca/groups?hl=en&threadm=lxndYBAcBJu1Ewqs%40insci....
co.uk&rnum=3&prev=/groups%3Fq%3Dupsizing%2Baccess%2BLEFT%2Bjoin%2Bgroup:comp
.databases.*%26hl%3Den%26selm%3DlxndYBAcBJu1Ewqs%2540insci.demon.co.uk%26rnu
m%3D3

Quote:

> > FJL

============================================================================

Quote:> > From: BP Margolin
> > Date Posted: 3/25/2002 1:13:00 PM

> > FJL,

My interpretation of this is that when you set up a join you can define
the direction of dependency. With 1 to many joins it is automatically
assumed that the dependency in a join with enforced referential
integrity is from 1 to Many.

In a 1 to 1 join I believe that the direction you drag the join boxes in
the relationship window defines which table must be populated first. As
I normally use 1:1 for subtyping I automatically drag from the main
table (e.g. tblEmployee) to the subtype table (e.g. tblManager). Doing
it the other weay round can (if I remember rightly) cause problems.

It's ages since this last happened to me and I'm too lazy to experiment
right now!
--
Albert Marshall
Database Developer
Marshall Le Botmel Ltd
01242 222017

Albert,

I believe you are confusing JOINs with PK / FK relationships.

The issue of PK / FK relationships is completely different from joins. One
can join tables without there being either an explicit or implicit PK / FK
relationship between the tables.

So, again, I'm requesting a code sample of an SQL query that uses a left or
a right inner join.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:>Albert,

>I believe you are confusing JOINs with PK / FK relationships.

Only insofar as the Acess user interface causes such confusion.

I'm pretty sure that the join direction option when you create a
relationship is merely the default join type to be used in queries
joining the two tables. The fact that MS decided to put it there can
cause the sort of confusion that this thread has illustrated.

Quote:

>The issue of PK / FK relationships is completely different from joins. One
>can join tables without there being either an explicit or implicit PK / FK
>relationship between the tables.

>So, again, I'm requesting a code sample of an SQL query that uses a left or
>a right inner join.

I didn't originate the concept in this thread, I merely tried to guess
at an explanation of what Doug meant by it. I think I slipped up in one
place in my terminology, but the general drift is probably clear enough.

Ooops! Should have been

    "when you set up a relationship you can define the
      direction of dependency."

Apply similar corrections furthe down.

Quote:> With 1 to many joins it is automatically
>> assumed that the dependency in a join with enforced referential
>> integrity is from 1 to Many.

>> In a 1 to 1 join I believe that the direction you drag the join boxes in
>> the relationship window defines which table must be populated first. As
>> I normally use 1:1 for subtyping I automatically drag from the main
>> table (e.g. tblEmployee) to the subtype table (e.g. tblManager). Doing
>> it the other weay round can (if I remember rightly) cause problems.

>> It's ages since this last happened to me and I'm too lazy to experiment
>> right now!
>> --
>> Albert Marshall
>> Database Developer
>> Marshall Le Botmel Ltd
>> 01242 222017

--
Albert Marshall
Database Developer
Marshall Le Botmel Ltd
01242 222017

Albert,

My point is that there is no such thing as a LEFT INNER JOIN or a RIGHT
INNER JOIN. There is only an INNER JOIN.

There does exist a LEFT OUTER JOIN and a RIGHT OUTER JOIN.

Quote:> I didn't originate the concept in this thread, I merely tried to guess
> at an explanation of what Doug meant by it. I think I slipped up in one
> place in my terminology, but the general drift is probably clear enough.

Unfortunately, particularly when dealing with RDBMS's and SQL, incorrect
terminology frequently reveals confusion about both databases and SQL. I'm
not saying that you suffer from this, but I am trying to make sure that
individuals who read this newsgroup do not "walk away" with an inaccurate
understanding of SQL Server. I'm prompted to do this because I remember when
I was learning SQL Server that a wealth of misinformation was supplied to
me, and it took me some time to un-learn it.

I fully understand that we try to "simplify" things for newbies, but I do
believe that one needs to be accurate even when simplifying. I also
understand that there is a fine line to be drawn re: the amount of detail
that can be supplied within the context of a newsgroup. Perhaps we are
simply drawing the line at different places   :-)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

The Relationship "direction" is determined by the Access Wizard, and is
dependent upon the properties of the indexes that were created for the
related fields in the tables. If you join two fields that are Unique, then
the only possible relationship is 1:1, etc. (The direction of "dragging"
when drawing the line is irrelevant.)

As you pointed out, the fact that the same graphic technique is used in
Access to indicate 1) the cardinality of a relationship, and 2) the
direction of an outer join can lead the unsuspecting into thinking they are
the same thing.

Clive

Quote:>The Relationship "direction" is determined by the Access Wizard, and is
>dependent upon the properties of the indexes that were created for the
>related fields in the tables. If you join two fields that are Unique, then
>the only possible relationship is 1:1, etc. (The direction of "dragging"
>when drawing the line is irrelevant.)

Clive,
Sorry, but you are wrong. The direction of drag controls which table
must be populated first. I've just carried out this experiment in Access
97:

Create two tables (tblTest1 and tblTest2) each with a number field (and
others if you want).

Make the number field the PK in each case.

Create a relationship in the relationships window by dragging the PK
field from tblTest1 to tblTest2 and apply referential integrity.

Try making an entry in tblTest2.

Access complains that there is no matching entry in tblTest1.

If you start by making an entry in tblTest1 and then a matching entry in
tblTest2 everything works fine.

This shows that the direction of dragging is not irrelevant. The
relationship between the two tables is actually

        One to (Zero or One)

This could be seen as conceptually similar to the idea of full or outer
joins, and is what I was guessing that Doug meant.

--
Albert Marshall
Database Developer
Marshall Le Botmel Ltd
01242 222017

Albert,

  What you say is right, but I'll agree with those who have questioned
the sanity of the Access interface for relationships.  The default is that
referential integrity is not enforced (I'm not quite sure what the word
relationship means then), and there is a button on the Edit Relationship
dialog called "Join Type...", which gives three choices of rows to
include.

  Ok, I won't be quite so stubborn.  The best I can tell is that in Access,
a relationship says that a column in one table and a column in another
table represent the same thing, and if I put both tables into a query, I
want Access to assume I want the columns matched up, whether I
specifically ask for it or not.  The "join type" of a relationship further
specifies what Access should do without asking.

  Never having used the upsizing wizard, I thought why not try, and
did so with the two tables you suggest, linked with an arrow that does
not enforce referential integrity.  After answering a few questions,
the attempt to connect/upsize to SQL Server produced
a dialog box titled Upsizing Wizard that said "Overflow" and included
an OK button.  The two tables had 5 rows between them, and the values
were between 1 and 5.

  So I don't know how this would have upsized.

Steve Kass
Drew University