473,320 Members | 1,810 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Bad Query?

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
15 2518
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: jaysonsch | last post by:
Hello! I am having some problems with a database query that I am trying to do. I am trying to develop a way to search a database for an entry and then edit the existing values. Upon submit, the...
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
3
by: Harvey | last post by:
Hi, I try to write an asp query form that lets client search any text-string and display all pages in my web server that contain the text. I have IIS 6.0 on a server 2003. The MSDN site says...
4
by: Diamondback | last post by:
I have two tables, WIDGETS and VERSIONS. The WIDGETS table has descriptive information about the widgets while the VERSIONS table contains IDs relating to different iterations of those widgets...
14
by: Dave Thomas | last post by:
If I have a table set up like this: Name | VARCHAR Email | VARCHAR Age | TINYINT | NULL (Default: NULL) And I want the user to enter his or her name, email, and age - but AGE is optional. ...
0
by: starace | last post by:
I have designed a form that has 5 different list boxes where the selections within each are used as criteria in building a dynamic query. Some boxes are set for multiple selections but these list...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
4
by: Stan | last post by:
I am using MS Office Access 2003 (11.5614). My basic question is can I run a query of a query datasheet. I want to use more that one criteria and can not get that query to work. I thought I...
6
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.