By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

Bad Query?

P: n/a
GTi
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
Jul 23 '05 #1
Share this Question
Share on Google+
15 Replies


P: n/a
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)
Jul 23 '05 #2

P: n/a
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)
Jul 23 '05 #3

P: n/a
GTi
"Ross Presser" <rp******@imtek.com> skrev i melding
news:uw***************@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?


Jul 23 '05 #4

P: n/a
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" <bi**@gates.com> wrote in message news:3nFDd.668$VR2.22@amstwist00...
"Ross Presser" <rp******@imtek.com> skrev i melding
news:uw***************@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?

Jul 23 '05 #5

P: n/a
GTi
One guy told me that top posting is perilous, so look at the bottom.

"David Rawheiser" <ra*******@hotmail.com> skrev i melding
news:KJ*******************@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" <bi**@gates.com> wrote in message
news:3nFDd.668$VR2.22@amstwist00...
"Ross Presser" <rp******@imtek.com> skrev i melding
news:uw***************@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).


Jul 23 '05 #6

P: n/a
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)
Jul 23 '05 #7

P: n/a
GTi
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> skrev i melding
news:5s********************************@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!


Jul 23 '05 #8

P: n/a
GTi
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> skrev i melding
news:5s********************************@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?


Jul 23 '05 #9

P: n/a
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)
Jul 23 '05 #10

P: n/a
GTi
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> skrev i melding
news:on********************************@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


Jul 23 '05 #11

P: n/a
GTi (bi**@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #12

P: n/a
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)
Jul 23 '05 #13

P: n/a
GTi
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> skrev i melding
news:iv********************************@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/specif...foreignkey.htm
(sorry - should mention the source)
Google is always a good start to begin.

Thanks for pointing that out.

Jul 23 '05 #14

P: n/a
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/specif...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)
Jul 23 '05 #15

P: n/a
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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.