Bad Query?

I have a query like:

SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
"ContactTable1"."Name2" FROM "ContactTable","ProjectMembers","ContactTable"
"ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
("ContactTable"."RefContact" = "ContactTable1"."NDX")

This works as expected.
But I am concerned about this if the
"ContactTable"."RefContact" = "ContactTable1"."NDX"
is FALSE. (the RefContact is missing or broked)
The whole query fails.

Is there any way to prevent that the whole quey fails and only return NULL
fields?

Regards
GTiOn Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:

> I have a query like:
> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
> "ContactTable1"."Name2" FROM "ContactTable","ProjectMembers","ContactTable"
> "ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
> ("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
> ("ContactTable"."RefContact" = "ContactTable1"."NDX")
> This works as expected.
> But I am concerned about this if the
> "ContactTable"."RefContact" = "ContactTable1"."NDX"
> is FALSE. (the RefContact is missing or broked)
> The whole query fails.
> Is there any way to prevent that the whole quey fails and only return NULL
> fields?
> Regards
> GTi

You want LEFT JOIN. While you're at it, change the other table-joining
phrase into an INNER JOIN. It makes it easier to understand which
conditions join tables together and which conditions filter the rows.

SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
"ContactTable1"."Name2"
FROM "ProjectMembers"
INNER JOIN "ContactTable"
ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
LEFT JOIN "ContactTable1"
ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
WHERE ("ProjectMembers"."ProjectNDX"=4)|||On Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:

>I have a query like:
>SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>"ContactTable1"."Name2" FROM "ContactTable","ProjectMembers","ContactTable"
>"ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
>("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
>("ContactTable"."RefContact" = "ContactTable1"."NDX")
>This works as expected.
>But I am concerned about this if the
>"ContactTable"."RefContact" = "ContactTable1"."NDX"
>is FALSE. (the RefContact is missing or broked)
>The whole query fails.
>Is there any way to prevent that the whole quey fails and only return NULL
>fields?

Hi GTi,

I don't really understand what you're trying to achieve. Please post
again, supplying
* Table structure (as CREATE TABLE statements, including all constraints
and properties but omitting irrelevant columns - see www.aspfaq.com/5006)
* Sample data that illustrates what you need to get done (posted as INSERT
statements)
* Expected output
* And a description of the business problem you're trying to solve.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Ross Presser" <rpresser@.imtek.com> skrev i melding
news:uw58xnrbmdjl$.dlg@.rpresser.invalid...
> On Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:
>> I have a query like:
>>
>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>> "ContactTable1"."Name2" FROM
>> "ContactTable","ProjectMembers","ContactTable"
>> "ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
>> ("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
>> ("ContactTable"."RefContact" = "ContactTable1"."NDX")
>>
>> This works as expected.
>> But I am concerned about this if the
>> "ContactTable"."RefContact" = "ContactTable1"."NDX"
>> is FALSE. (the RefContact is missing or broked)
>> The whole query fails.
>>
>> Is there any way to prevent that the whole quey fails and only return
>> NULL
>> fields?
>>
>> Regards
>> GTi
> You want LEFT JOIN. While you're at it, change the other table-joining
> phrase into an INNER JOIN. It makes it easier to understand which
> conditions join tables together and which conditions filter the rows.
> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
> "ContactTable1"."Name2"
> FROM "ProjectMembers"
> INNER JOIN "ContactTable"
> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
> LEFT JOIN "ContactTable1"
> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
> WHERE ("ProjectMembers"."ProjectNDX"=4)

At first I get this error:
ODBC Error: SQLSTATE = S1000, Native error code = 0
Unable to open table: ContactTable1.
No such table or object.

Then I changed it to:
SELECT ProjectMembers.RoleText, ProjectMembers.Description,
ContactTable.Name1, ContactTable.Name2, ContactTable1.Name1,
ContactTable1.Name2
FROM ProjectMembers, ContactTable, ContactTable1 as ContactTable
INNER JOIN ContactTable ON ProjectMembers.ContactNDX = ContactTable.NDX
LEFT JOIN ContactTable1 ON ContactTable.RefContact = ContactTable1.NDX
WHERE (ProjectMembers.ProjectNDX=4)

and get this error:
ODBC Error: SQLSTATE = 37000, Native error code = 0
Table reference [ContactTable] must be unique.

Now I'm a stuck newbie....

What I want...
I want to get a list of all contacts and ref. contacts to that contacts
(nested contacts) that
is connected to a project listed in the ProjectMembers table.
The original scripts works just fine, but I see a problem when a contact is
deleted
(and the ProjectMembers table is not updated) or/and when a contact don't
have
any refContact. Then the whole scrips fails.
If that happen I just want the xx.Name1, xx.Name2 fields left blank/NULL in
the script result
but returns the RoleText and Description from the ProjectMembers table.
Then "someone" can open the records and edit the correct contact.

OK?|||Do you mean that the table may be missing? or matching entries in the table?

If you mean entries in the table - a left join will work for ya.

if however the table is missing you will need to use dynamic sql to modify
your query,
or in a seperate step prior to your query make sure its there by creating it
if not.

"GTi" <bill@.gates.com> wrote in message news:3nFDd.668$VR2.22@.amstwist00...
> "Ross Presser" <rpresser@.imtek.com> skrev i melding
> news:uw58xnrbmdjl$.dlg@.rpresser.invalid...
>> On Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:
>>
>>> I have a query like:
>>>
>>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>>> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>>> "ContactTable1"."Name2" FROM
>>> "ContactTable","ProjectMembers","ContactTable"
>>> "ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
>>> ("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
>>> ("ContactTable"."RefContact" = "ContactTable1"."NDX")
>>>
>>> This works as expected.
>>> But I am concerned about this if the
>>> "ContactTable"."RefContact" = "ContactTable1"."NDX"
>>> is FALSE. (the RefContact is missing or broked)
>>> The whole query fails.
>>>
>>> Is there any way to prevent that the whole quey fails and only return
>>> NULL
>>> fields?
>>>
>>> Regards
>>> GTi
>>
>> You want LEFT JOIN. While you're at it, change the other table-joining
>> phrase into an INNER JOIN. It makes it easier to understand which
>> conditions join tables together and which conditions filter the rows.
>>
>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>> "ContactTable1"."Name2"
>> FROM "ProjectMembers"
>> INNER JOIN "ContactTable"
>> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
>> LEFT JOIN "ContactTable1"
>> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
>> WHERE ("ProjectMembers"."ProjectNDX"=4)
> At first I get this error:
> ODBC Error: SQLSTATE = S1000, Native error code = 0
> Unable to open table: ContactTable1.
> No such table or object.
> Then I changed it to:
> SELECT ProjectMembers.RoleText, ProjectMembers.Description,
> ContactTable.Name1, ContactTable.Name2, ContactTable1.Name1,
> ContactTable1.Name2
> FROM ProjectMembers, ContactTable, ContactTable1 as ContactTable
> INNER JOIN ContactTable ON ProjectMembers.ContactNDX = ContactTable.NDX
> LEFT JOIN ContactTable1 ON ContactTable.RefContact = ContactTable1.NDX
> WHERE (ProjectMembers.ProjectNDX=4)
> and get this error:
> ODBC Error: SQLSTATE = 37000, Native error code = 0
> Table reference [ContactTable] must be unique.
> Now I'm a stuck newbie....
>
> What I want...
> I want to get a list of all contacts and ref. contacts to that contacts
> (nested contacts) that
> is connected to a project listed in the ProjectMembers table.
> The original scripts works just fine, but I see a problem when a contact
> is deleted
> (and the ProjectMembers table is not updated) or/and when a contact don't
> have
> any refContact. Then the whole scrips fails.
> If that happen I just want the xx.Name1, xx.Name2 fields left blank/NULL
> in the script result
> but returns the RoleText and Description from the ProjectMembers table.
> Then "someone" can open the records and edit the correct contact.
> OK?
>|||One guy told me that top posting is perilous, so look at the bottom.

"David Rawheiser" <rawhide58@.hotmail.com> skrev i melding
news:KJQDd.82273$uM5.20030@.bgtnsc05-news.ops.worldnet.att.net...
> Do you mean that the table may be missing? or matching entries in the
> table?
> If you mean entries in the table - a left join will work for ya.
> if however the table is missing you will need to use dynamic sql to modify
> your query,
> or in a seperate step prior to your query make sure its there by creating
> it if not.
> "GTi" <bill@.gates.com> wrote in message
> news:3nFDd.668$VR2.22@.amstwist00...
>> "Ross Presser" <rpresser@.imtek.com> skrev i melding
>> news:uw58xnrbmdjl$.dlg@.rpresser.invalid...
>>> On Fri, 7 Jan 2005 20:34:02 +0100, GTi wrote:
>>>
>>>> I have a query like:
>>>>
>>>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>>>> "ContactTable"."Name1", "ContactTable"."Name2",
>>>> "ContactTable1"."Name1",
>>>> "ContactTable1"."Name2" FROM
>>>> "ContactTable","ProjectMembers","ContactTable"
>>>> "ContactTable1" WHERE ("ProjectMembers"."ProjectNDX"=4) AND
>>>> ("ProjectMembers"."ContactNDX" = "ContactTable"."NDX") AND
>>>> ("ContactTable"."RefContact" = "ContactTable1"."NDX")
>>>>
>>>> This works as expected.
>>>> But I am concerned about this if the
>>>> "ContactTable"."RefContact" = "ContactTable1"."NDX"
>>>> is FALSE. (the RefContact is missing or broked)
>>>> The whole query fails.
>>>>
>>>> Is there any way to prevent that the whole quey fails and only return
>>>> NULL
>>>> fields?
>>>>
>>>> Regards
>>>> GTi
>>>
>>> You want LEFT JOIN. While you're at it, change the other table-joining
>>> phrase into an INNER JOIN. It makes it easier to understand which
>>> conditions join tables together and which conditions filter the rows.
>>>
>>> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
>>> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
>>> "ContactTable1"."Name2"
>>> FROM "ProjectMembers"
>>> INNER JOIN "ContactTable"
>>> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
>>> LEFT JOIN "ContactTable1"
>>> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
>>> WHERE ("ProjectMembers"."ProjectNDX"=4)
>>
>> At first I get this error:
>> ODBC Error: SQLSTATE = S1000, Native error code = 0
>> Unable to open table: ContactTable1.
>> No such table or object.
>>
>> Then I changed it to:
>> SELECT ProjectMembers.RoleText, ProjectMembers.Description,
>> ContactTable.Name1, ContactTable.Name2, ContactTable1.Name1,
>> ContactTable1.Name2
>> FROM ProjectMembers, ContactTable, ContactTable1 as ContactTable
>> INNER JOIN ContactTable ON ProjectMembers.ContactNDX = ContactTable.NDX
>> LEFT JOIN ContactTable1 ON ContactTable.RefContact = ContactTable1.NDX
>> WHERE (ProjectMembers.ProjectNDX=4)
>>
>> and get this error:
>> ODBC Error: SQLSTATE = 37000, Native error code = 0
>> Table reference [ContactTable] must be unique.
>>
>> Now I'm a stuck newbie....
>>
>>
>> What I want...
>> I want to get a list of all contacts and ref. contacts to that contacts
>> (nested contacts) that
>> is connected to a project listed in the ProjectMembers table.
>> The original scripts works just fine, but I see a problem when a contact
>> is deleted
>> (and the ProjectMembers table is not updated) or/and when a contact don't
>> have
>> any refContact. Then the whole scrips fails.
>> If that happen I just want the xx.Name1, xx.Name2 fields left blank/NULL
>> in the script result
>> but returns the RoleText and Description from the ProjectMembers table.
>> Then "someone" can open the records and edit the correct contact.
>>
>> OK?
>>
>>
>>
>>

I mean that if the matching entries in the table is missing (not the whole
table).
Example:
ContactTable.RefContact = ContactTable1.NDX

if ContactTable.RefContact IS 0 / NULL
or ContactTable.RefContact have a value to a non existing record (NDX).|||On Sat, 8 Jan 2005 00:58:59 +0100, GTi wrote:

>At first I get this error:
>ODBC Error: SQLSTATE = S1000, Native error code = 0
>Unable to open table: ContactTable1.
>No such table or object.
(snip)

Hi GTi,

Your original query was very hard to read, due to formatting, over-using
of double-quotes and omitting the AS keyword (I know it's optional, but
including it just makes your query so much better to read!)

Try modifying Ross' suggestion to:

SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
"ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
"ContactTable1"."Name2"
FROM "ProjectMembers"
INNER JOIN "ContactTable"
ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
LEFT JOIN "ContactTable" AS "ContactTable1"
ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
WHERE ("ProjectMembers"."ProjectNDX"=4)

Or, better yet:

SELECT ProjectMembers.RoleText, ProjectMembers.Description,
ContactTable.Name1, ContactTable.Name2,
ContactTable1.Name1, ContactTable1.Name2
FROM ProjectMembers
INNER JOIN ContactTable
ON ProjectMembers.ContactNDX = ContactTable.NDX
LEFT JOIN ContactTable AS ContactTable1
ON ContactTable.RefContact = ContactTable1.NDX
WHERE ProjectMembers.ProjectNDX = 4

Or (even better in my opinion, but some would disagree):

SELECT p.RoleText, p.Description,
c.Name1, c.Name2,
c1.Name1, c1.Name2
FROM ProjectMembers AS p
INNER JOIN ContactTable AS c
ON c.NDX = p.ContactNDX
LEFT JOIN ContactTable AS c1
ON c1.NDX = c.RefContact
WHERE p.ProjectNDX = 4

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> skrev i melding
news:5sj0u092kva6ste7jffrf6m63ojgva248n@.4ax.com...
> On Sat, 8 Jan 2005 00:58:59 +0100, GTi wrote:
>>At first I get this error:
>>ODBC Error: SQLSTATE = S1000, Native error code = 0
>>Unable to open table: ContactTable1.
>>No such table or object.
> (snip)
> Hi GTi,
> Your original query was very hard to read, due to formatting, over-using
> of double-quotes and omitting the AS keyword (I know it's optional, but
> including it just makes your query so much better to read!)
> Try modifying Ross' suggestion to:
> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
> "ContactTable1"."Name2"
> FROM "ProjectMembers"
> INNER JOIN "ContactTable"
> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
> LEFT JOIN "ContactTable" AS "ContactTable1"
> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
> WHERE ("ProjectMembers"."ProjectNDX"=4)
> Or, better yet:
> SELECT ProjectMembers.RoleText, ProjectMembers.Description,
> ContactTable.Name1, ContactTable.Name2,
> ContactTable1.Name1, ContactTable1.Name2
> FROM ProjectMembers
> INNER JOIN ContactTable
> ON ProjectMembers.ContactNDX = ContactTable.NDX
> LEFT JOIN ContactTable AS ContactTable1
> ON ContactTable.RefContact = ContactTable1.NDX
> WHERE ProjectMembers.ProjectNDX = 4
> Or (even better in my opinion, but some would disagree):
> SELECT p.RoleText, p.Description,
> c.Name1, c.Name2,
> c1.Name1, c1.Name2
> FROM ProjectMembers AS p
> INNER JOIN ContactTable AS c
> ON c.NDX = p.ContactNDX
> LEFT JOIN ContactTable AS c1
> ON c1.NDX = c.RefContact
> WHERE p.ProjectNDX = 4
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

I love the last one, It look nice and it is easier to read.
And it give me the result I wanted.
If I change the line
ON c1.NDX = c.RefContact
to
ON c1.NDX = 9999999 // or 0

Is still gives me the records but with no refContact.

Love it.
Thanks Hugo!|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> skrev i melding
news:5sj0u092kva6ste7jffrf6m63ojgva248n@.4ax.com...
> On Sat, 8 Jan 2005 00:58:59 +0100, GTi wrote:
>>At first I get this error:
>>ODBC Error: SQLSTATE = S1000, Native error code = 0
>>Unable to open table: ContactTable1.
>>No such table or object.
> (snip)
> Hi GTi,
> Your original query was very hard to read, due to formatting, over-using
> of double-quotes and omitting the AS keyword (I know it's optional, but
> including it just makes your query so much better to read!)
> Try modifying Ross' suggestion to:
> SELECT "ProjectMembers"."RoleText", "ProjectMembers"."Description",
> "ContactTable"."Name1", "ContactTable"."Name2", "ContactTable1"."Name1",
> "ContactTable1"."Name2"
> FROM "ProjectMembers"
> INNER JOIN "ContactTable"
> ON "ProjectMembers"."ContactNDX" = "ContactTable"."NDX"
> LEFT JOIN "ContactTable" AS "ContactTable1"
> ON "ContactTable"."RefContact" = "ContactTable1"."NDX"
> WHERE ("ProjectMembers"."ProjectNDX"=4)
> Or, better yet:
> SELECT ProjectMembers.RoleText, ProjectMembers.Description,
> ContactTable.Name1, ContactTable.Name2,
> ContactTable1.Name1, ContactTable1.Name2
> FROM ProjectMembers
> INNER JOIN ContactTable
> ON ProjectMembers.ContactNDX = ContactTable.NDX
> LEFT JOIN ContactTable AS ContactTable1
> ON ContactTable.RefContact = ContactTable1.NDX
> WHERE ProjectMembers.ProjectNDX = 4
> Or (even better in my opinion, but some would disagree):
> SELECT p.RoleText, p.Description,
> c.Name1, c.Name2,
> c1.Name1, c1.Name2
> FROM ProjectMembers AS p
> INNER JOIN ContactTable AS c
> ON c.NDX = p.ContactNDX
> LEFT JOIN ContactTable AS c1
> ON c1.NDX = c.RefContact
> WHERE p.ProjectNDX = 4
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Hugo,
I can improve it if I only use LEFT JOIN:
LEFT JOIN ContactTable AS c
LEFT JOIN ContactTable AS c1

LEFT JOIN:
"The LEFT JOIN returns all the rows from the first table , even if there are
*no matches* in the other tables"

INNER JOIN:
"The INNER JOIN returns all rows from both tables where there is a match. If
there are rows in ProjectMembers that do not have matches in ContactTable,
those rows will *not* be listed. "

http://www.w3schools.com/sql/sql_join.asp

If so, I think I got the understanding of the JOINING stuff...
Or?|||On Sat, 8 Jan 2005 23:32:47 +0100, GTi wrote:

(snip)
>Hugo,
>I can improve it if I only use LEFT JOIN:
>LEFT JOIN ContactTable AS c
>LEFT JOIN ContactTable AS c1

Hi GTi,

Whether that's an improvement or not depends on your requirements - if one
of the join types would always be better, the other wouldn't have been
implemented. :-)

The first question is: does your business allow ProjectMembers not to have
a ContactNDX (whatever that may be)? If the answer is no, then the second
question is: are you concerned that your table might have ProjectMembers
without ContactNDX? If the answer to this question is yes, then your table
design is failing to enforce a business rule. You should first take steps
to find and correct any rows with data in violation of the business rule,
then set a NOT NULL constraint on the ContactNDX column to make sure that
the business rule is enforced henceforth.

The first question is: does your business allow ProjectMembers to have a
ContactNDX that is not present in the ContactTable table? If the answer is
no, then the second question is: are you concerned that your table might
have ProjectMembers with a ContactNDX that's missing from the ContactTable
table? If the answer to this question is yes, then your table design is
failing to enforce a business rule. You should first take steps to find
and correct any rows with data in violation of the business rule, then set
a FOREIGN KEY constraint on the ContactNDX column to make sure that the
business rule is enforced henceforth.

You now have ensured that your business rules are properly enforced by
database constraints. Now, you can use these business rules to decide how
to write your query. Again, we start with a question: Will every row in
the ProjectMembers table always have a value for ContactNDX, AND will this
value always be present in the ContactTable table? If the answer is yes,
then there is no need to use a LEFT JOIN here. The results will not be any
different from those of an INNER JOIN, but the query will be harder to
process for SQL Server.
However, if the answer is that there can be rows in ProjectMembers with no
matching row in ContactTable (either because ContactMDX is NULL or because
it is not present in ContactTable), then the next question would be if
these rows need to be included in the results of your query. If they
should appear (with NULLS in the columns from ContactTable), then you need
a LEFT JOIN; if they should not appear, then you must use INNER JOIN.

</lecture
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> skrev i melding
news:ono0u09b83iuknh5voi6bk5pc8sa9sjhbe@.4ax.com...
> On Sat, 8 Jan 2005 23:32:47 +0100, GTi wrote:
> (snip)
>>
>>Hugo,
>>I can improve it if I only use LEFT JOIN:
>>LEFT JOIN ContactTable AS c
>>LEFT JOIN ContactTable AS c1
> Hi GTi,
> Whether that's an improvement or not depends on your requirements - if one
> of the join types would always be better, the other wouldn't have been
> implemented. :-)
> The first question is: does your business allow ProjectMembers not to have
> a ContactNDX (whatever that may be)? If the answer is no, then the second
> question is: are you concerned that your table might have ProjectMembers
> without ContactNDX? If the answer to this question is yes, then your table
> design is failing to enforce a business rule. You should first take steps
> to find and correct any rows with data in violation of the business rule,
> then set a NOT NULL constraint on the ContactNDX column to make sure that
> the business rule is enforced henceforth.
> The first question is: does your business allow ProjectMembers to have a
> ContactNDX that is not present in the ContactTable table? If the answer is
> no, then the second question is: are you concerned that your table might
> have ProjectMembers with a ContactNDX that's missing from the ContactTable
> table? If the answer to this question is yes, then your table design is
> failing to enforce a business rule. You should first take steps to find
> and correct any rows with data in violation of the business rule, then set
> a FOREIGN KEY constraint on the ContactNDX column to make sure that the
> business rule is enforced henceforth.
> You now have ensured that your business rules are properly enforced by
> database constraints. Now, you can use these business rules to decide how
> to write your query. Again, we start with a question: Will every row in
> the ProjectMembers table always have a value for ContactNDX, AND will this
> value always be present in the ContactTable table? If the answer is yes,
> then there is no need to use a LEFT JOIN here. The results will not be any
> different from those of an INNER JOIN, but the query will be harder to
> process for SQL Server.
> However, if the answer is that there can be rows in ProjectMembers with no
> matching row in ContactTable (either because ContactMDX is NULL or because
> it is not present in ContactTable), then the next question would be if
> these rows need to be included in the results of your query. If they
> should appear (with NULLS in the columns from ContactTable), then you need
> a LEFT JOIN; if they should not appear, then you must use INNER JOIN.
> </lecture>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Hi Hugo,

ProjectMembers.ContactNDX always have a pointer to Contact.NDX

FOREIGN KEY Definition:
"A foreign key is a field in a relational table that matches the primary key
column of another table. The foreign key can be used to cross-reference
tables."

I have to learn about what FOREIGN KEY is and what it actual does.
Do it prevent deleting a record in the ContactTable.NDX if it have a
reference in ProjectMembers.ContactNDX ?
If it does, how does it alarm the user.
Correction: How can my program (C++) handle it and give the end user a well
explained error message?
My test project will give the end user full access to the database,
creating, modifying and deleting tables and records as needed.
So the possibility for the end user to delete a contacts is present.
So how can my program handle it?
My thought is to handle it so flexible as possible, broken links is
displayed as broken (in my program).
I'm sure there is SQL functions for it already built in, but it will be
captured in my "learning curve"...

Best regards,
GTi|||GTi (bill@.gates.com) writes:
> FOREIGN KEY Definition:
> "A foreign key is a field in a relational table that matches the primary
> key column of another table. The foreign key can be used to
> cross-reference tables."
> I have to learn about what FOREIGN KEY is and what it actual does.
> Do it prevent deleting a record in the ContactTable.NDX if it have a
> reference in ProjectMembers.ContactNDX ?

Yes. Here is a simple example:

CREATE TABLE customers (
customerid int NOT NULL,
customername nvarchar(50) NOT NULL,
CONSTRAINT pk_customers PRIMARY KEY (customerid))
go
CREATE TABLE orders (
orderid int NOT NULL,
customerid int NOT NULL,
orderdate datetime NOT NULL,
CONSTRAINT pk_orders PRIMARY KEY (orderid),
CONSTRAINT fk_order_customer FOREIGN KEY (customerid)
REFERENCES customers(customerid))
go
INSERT customers (customerid, customername)
VALUES (1, 'Don Preston')
INSERT customers (customerid, customername)
VALUES (2, 'Jimmy Carl Black')
INSERT customers (customerid, customername)
VALUES (4, 'Bunk Gardner')
go
INSERT orders (orderid, customerid, orderdate)
VALUES (1, 1, '20041212') -- Runs fine
INSERT orders (orderid, customerid, orderdate)
VALUES (2, 3, '20050106') -- Fails, no customerid = 3
go
DELETE customers WHERE customerid = 4 -- Runs fine
DELETE customers WHERE customerid = 1 -- Fails, since there is an order
go
DROP TABLE orders
DROP TABLE customers

> If it does, how does it alarm the user.

If you run the above, you will see that the error message is fairly
generic.

> Correction: How can my program (C++) handle it and give the end user a
> well explained error message?

In the above example, your order registration form should only let
you enter customer in the database. Most business have to many customers
to fit all in a drop-down box, but you get the idea. Basically, the
user never the enters the customerid, or if he does, he first gets to
see all details about the customer, to see that he has the right guy.

For deletion, your app would have to check for existing orders when
the user presses the delete buttom. Or your GUI would be even slicker
and not enable the delete button if there are orders.

Thus, the error message from SQL Server should never hit the user in
the face; the constraint is there to protect the database against a
fauly application.

> My test project will give the end user full access to the database,
> creating, modifying and deleting tables and records as needed.
> So the possibility for the end user to delete a contacts is present.
> So how can my program handle it?

Many applications give the users a sheltered environment where they
don't see tables as such, even less can create any.

If you plan to give your users such flexibility, they will also get a
rougher environment, and it is inevitable that they will see error
messages from SQL Server, that not always are that comprehensible.
The particular message about constraint violation is very generic, and
assumes that the person who reads it have full knowledge of the data model.
Your program could trap the message and then try to interpret what it
means, but that's a quite complex task.

An alternative in this case, is not use DRI (Declarative Referential
Integrity) like above, but instead have all checks in triggers. This
requires more programming, is more prone to errors, takes more
resources, but permits for customised error messages like
"Cannot delete customer, there are orders".

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 9 Jan 2005 00:57:47 +0100, GTi wrote:

>FOREIGN KEY Definition:
>"A foreign key is a field in a relational table that matches the primary key
>column of another table. The foreign key can be used to cross-reference
>tables."

Hi GTi,

May I ask you where you found this definition?

Personally, I'd be very wary of any text book that uses the term "field"
instead of "column" in descriptions of the relational model.
You'll find fileds on flat files and in some non-relational databases; in
a relational database, "column" is THE ONLY correct term. I usually don't
comment the mis-use of this terminology when people ask questions, but if
this is indeed a straight quote from a text-book, then it shouldn't go
uncommented!!

Even worse is that this definition seems to imply that a foreign key can't
span columns, but they definitely can:

CREATE TABLE Employees
(EmpID int NOT NULL,
EmpName varchar(50) NOT NULL,
-- more columns
PRIMARY KEY (EmpID)
)
go
CREATE TABLE Projects
(ProjID int NOT NULL,
ProjName varchar(25) NOT NULL,
-- more columns
PRIMARY KEY (ProjID)
)
go
CREATE TABLE Assignments
(EmpID int NOT NULL,
ProjID int NOT NULL,
AssStart smalldatetime NOT NULL,
AssEnd smalldatetime DEFAULT NULL,
PRIMARY KEY (EmpID, ProjID),
FOREIGN KEY (EmpID) REFERENCES Employees,
FOREIGN KEY (ProjID) REFERENCES Projects,
CHECK (AssEnd > AssStart)
)
go
CREATE TABLE TimeSheet
(EmpID int NOT NULL,
TSStart smalldatetime NOT NULL,
TSEnd smalldatetime NOT NULL,
TimeSpent AS CAST(DATEDIFF(minute, TSStart, TSEnd) / 60.0 AS
decimal (3,2)),
ProjID int NOT NULL,
PRIMARY KEY (EmpID, TSStart),
UNIQUE (EmpID, TSEnd),
-- The line below is the proof the foreign keys can span multiple columns
FOREIGN KEY (EmpID, ProjID) REFERENCES Assignments,
CHECK (TSEnd > TSStart),
CHECK (DATEDIFF(day, TSStart, TSEnd) = 0)
)
go

I realise that the above doesn't answer any of the questions you asked,
but I think that has alrteady been taken care of by Erland (thanks,
Erland!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> skrev i melding
news:iv63u0hil9rshu7oa1opovknephvaljj5k@.4ax.com...
> On Sun, 9 Jan 2005 00:57:47 +0100, GTi wrote:
>>FOREIGN KEY Definition:
>>"A foreign key is a field in a relational table that matches the primary
>>key
>>column of another table. The foreign key can be used to cross-reference
>>tables."
> Hi GTi,
> May I ask you where you found this definition?
> Personally, I'd be very wary of any text book that uses the term "field"
> instead of "column" in descriptions of the relational model.
> You'll find fileds on flat files and in some non-relational databases; in
> a relational database, "column" is THE ONLY correct term. I usually don't
> comment the mis-use of this terminology when people ask questions, but if
> this is indeed a straight quote from a text-book, then it shouldn't go
> uncommented!!
> Even worse is that this definition seems to imply that a foreign key can't
> span columns, but they definitely can:
> CREATE TABLE Employees
> (EmpID int NOT NULL,
> EmpName varchar(50) NOT NULL,
> -- more columns
> PRIMARY KEY (EmpID)
> )
> go
> CREATE TABLE Projects
> (ProjID int NOT NULL,
> ProjName varchar(25) NOT NULL,
> -- more columns
> PRIMARY KEY (ProjID)
> )
> go
> CREATE TABLE Assignments
> (EmpID int NOT NULL,
> ProjID int NOT NULL,
> AssStart smalldatetime NOT NULL,
> AssEnd smalldatetime DEFAULT NULL,
> PRIMARY KEY (EmpID, ProjID),
> FOREIGN KEY (EmpID) REFERENCES Employees,
> FOREIGN KEY (ProjID) REFERENCES Projects,
> CHECK (AssEnd > AssStart)
> )
> go
> CREATE TABLE TimeSheet
> (EmpID int NOT NULL,
> TSStart smalldatetime NOT NULL,
> TSEnd smalldatetime NOT NULL,
> TimeSpent AS CAST(DATEDIFF(minute, TSStart, TSEnd) / 60.0 AS
> decimal (3,2)),
> ProjID int NOT NULL,
> PRIMARY KEY (EmpID, TSStart),
> UNIQUE (EmpID, TSEnd),
> -- The line below is the proof the foreign keys can span multiple columns
> FOREIGN KEY (EmpID, ProjID) REFERENCES Assignments,
> CHECK (TSEnd > TSStart),
> CHECK (DATEDIFF(day, TSStart, TSEnd) = 0)
> )
> go
>
> I realise that the above doesn't answer any of the questions you asked,
> but I think that has alrteady been taken care of by Erland (thanks,
> Erland!)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

> May I ask you where you found this definition?
http://databases.about.com/cs/speci.../foreignkey.htm
(sorry - should mention the source)
Google is always a good start to begin.

Thanks for pointing that out.|||On Sun, 9 Jan 2005 22:56:48 +0100, GTi wrote:

(snip)
>> May I ask you where you found this definition?
>http://databases.about.com/cs/speci.../foreignkey.htm
>(sorry - should mention the source)
>Google is always a good start to begin.

Hi GTi,

Yes - just as long as you're aware that there's no quality control on the
internet. Always double-check, never take anything for granted!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Personally, I'd be very wary of any text book that uses the term "field"
> instead of "column" in descriptions of the relational model.
> You'll find fileds on flat files and in some non-relational databases; in
> a relational database, "column" is THE ONLY correct term. I usually don't
> comment the mis-use of this terminology when people ask questions, but if
> this is indeed a straight quote from a text-book, then it shouldn't go
> uncommented!!

Yeah, I know we are supposed to be snobby and not say "field" or "record",
but "column" and "row" are just different names for the same thing.

Recently I had all reason to damn my own usage. I was writing about how
to use a data-modelling tool, and there is one dialog where you enter
columns for a table. The dialog is laid out with a grid, where you
enter one column one each row, and then there are differnt columns
in that grid which describe different properties - name, domain,
nullability, description etc - that the table columns have.

I really should have written "fields" throughout.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp