Puzzling Primary Key Problem

Hi,

I wonder if anybody encountered before with tables which has records with some of the primary keys as null values?

Currently I've found a number of records which isn't suppose to be inserted in the first place from a table.

My table size is over a few millions and currently using SQL Server Standard version

Seems like the primary key got corrupted or something...

Anybody encountered this before??

-deb-If the primary key of the table is autogenerated then you can use

DBCC CHECKIDENT

for Checking the current identity value for the specified table and, if needed, corrects the identity value.|||Thanks but I don't think that's the case because it happens to a non identity column.
By Default even before inserting it SQL Server should have prompted a Primary Key Violation Error but in this case it doesn't and proceed to insert the value into the column as null.|||Can you execute a DBCC Checktable statement on the table and verify the results.|||Will try and see and let you know as the db is at customer's site.
Thanks
:)|||do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

here is what [BOL] has to say on the matter

"When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft SQL Server 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

No null values
No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
"

so i tried this

create table nulltesttable
(
nullcolumn varchar(10) not null
)

and added three rows

insert nulltesttable values (char(9))
insert nulltesttable values (char(13))
insert nulltesttable values (char(32))

then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

select Char(nullcolumn) as 'Null column'
from nulltesttable

there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...|||Thanks... actually that was what I suspect also due to the reason the values are from barcode and sometimes the reader can return some rubbish that is not visible. There are a few records which this rubbish data.

But still I need to look for other possibilities. For my case if I were to select out using a select statement

select * from table
where column is null

It'll return records. :)

That's the very funny thing*sigh*

Originally posted by Ruprect
do the users provide their own values for example could they press the spacebar or does your front end add characters using a ascii function like char(xx)

here is what [BOL] has to say on the matter

"When a PRIMARY KEY constraint is added to an existing column or columns in the table, Microsoft SQL Server 2000 checks the existing data in the columns to ensure that the existing data follows the rules for primary keys:

No null values
No duplicate values

If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.
"

so i tried this

create table nulltesttable
(
nullcolumn varchar(10) not null
)

and added three rows

insert nulltesttable values (char(9))
insert nulltesttable values (char(13))
insert nulltesttable values (char(32))

then i applied the pk with nocheck and it applied as long as i didnt duplicate the ascii chars in the columns

i cant say what your issue is but if you try this query it could at least lead you to eliminate this as a problem

select Char(nullcolumn) as 'Null column'
from nulltesttable

there are a very limited ascii chars that are invisible so if you have a large # of rows that appear to be null then this is probably not your issue either way thanks for the exercise...sql

Put record in database.

Hello,
I have a database where each record has the following fields:
[id], [datetime], [name], [price]
I have an array with 1000 records which I need to save in the array.
When placing an array record, in the database, I need to check if there
is already one with the same datetime. If there is I want to replace the
name and price of that record. If there isn't I want to create a new
record.
Is this possible?
Thanks,
MiguelShapper,
I think you mean table?
I once wrote a simple DTS package to do what you're asking that replaced a
complex 8 page stored procedure using cursors. Took the op time from 20
hours to < 1 hour. I'd look at DTS for your UPDATE/INSERT.
HTH
Jerry
"Shapper" <mdmoura*NOSPAM*@.gmail.*DELETE2SEND*com> wrote in message
news:uVmnle00FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have a database where each record has the following fields:
> [id], [datetime], [name], [price]
> I have an array with 1000 records which I need to save in the array.
> When placing an array record, in the database, I need to check if there is
> already one with the same datetime. If there is I want to replace the name
> and price of that record. If there isn't I want to create a new record.
> Is this possible?
> Thanks,
> Miguel
>|||You will need to use an IF Exist condition. But what do you want to change
the name and price to?
"Shapper" wrote:

> Hello,
> I have a database where each record has the following fields:
> [id], [datetime], [name], [price]
> I have an array with 1000 records which I need to save in the array.
> When placing an array record, in the database, I need to check if there
> is already one with the same datetime. If there is I want to replace the
> name and price of that record. If there isn't I want to create a new
> record.
> Is this possible?
> Thanks,
> Miguel
>

Push referenced records

Hi,
I am facing the following problem. Please help me to solve it.
My question is: How to force the replication engine to include not only
the updated records but the relevant records as well?
I am using merge replication (SQL2kSP3 with SQL2kCE) with row filtering.
I many cases i have to replicate tables with "many to many"
relationship, where i operate the connection table (BOOKPERSON in the
example bellow). All the rows are filtered by PERSON_ID through out the
database.
Eg.
PERSON records to send:
select * from person where person_id = HOST_NAME()
BOOK records to send:
select * from book inner join bookperson on book.book_id =
bookperson.book_id and bookperson.person_id = HOST_NAME()
BOOKPERSON records to send:
select * from bookperson where person_id = HOST_NAME()
My problem is the following. The newly initialized subsciption
replicates fine, but in case of inserting 1 row to the table BOOKPERSON
(which means associating a book with a person) causes the replication to
fail, because the it replicates only the inserted BOOKPERSON record, and
does not replicate the the relevant BOOK record that is referenced now
by the BOOKPERSON record.
So my question is: How to force the replication engine to include not
only the updated records but the relevant records as well?
Thanks in advance
Pierre
The relevant schema is as follows:
CREATE TABLE Book (
BOOK_ID int not null,
BOOK_TITLE varchar(30) not null,
constraint PK_BOOK primary key clustered (BOOK_ID)
)
CREATE TABLE Person (
PERSON_ID int not null,
PERSON_NAME varchar(30) not null,
constraint PK_PERSON primary key clustered (PERSON_ID)
)
CREATE TABLE BookPerson (
BOOK_ID int not null,
PERSON_ID int not null,
constraint PK_BOOKPERSON primary key clustered (BOOK_ID, PERSON_ID)
)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_BOOKPERSON foreign key (BOOK_ID)
references BOOK (BOOK_ID)
ALTER TABLE BookPerson
ADD CONSTRAINT FK_PERSONBOOK foreign key (PERSON_ID)
references PERSON (PERSON_ID)
Pierre,
presumably the personid value for a bookperson and book are not really
referring to the same entity (a person can be related to an individual book
as eg an author and as a reader), otherwise the related book would already
be replicated? In that case I'd say that the filter clause on the Books
article is incorrect. The simplest way would be to drop this filter and
replicate all the books.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I think I forgot to tell that we have thousands of records in the "BOOK"
table, and our subscribers have very limited storage capacity (~1000
thousand book records).
The relation between the two tables (person, book) is not like "authors
of a book" but "books 'thouched' by person", so there is a real M:N
relationship.
Meanwhile found the solution to my problem. I think I have to create
triggers (for insert, delete and update) on the table BookPerson which
will use a stored procedure (sp_mergedummyupdate) to force the
replication engine to put the relevant book records into the publication.
Am I right?
regards
Pierre
|||Pierre,
this sounds correct. Actually I misread your original post and didn't notice
the inner join, so was thinking that the PersonID was a FK directly to the
books table as an author. You'll need to make sure that the FK relationship
is 'Not For Replication', as you can't guarantee the replication order (in
SQL 2005 the default is PK then FK records but not in SQL 2000).
Regards,
Paul Ibison
|||Thank you for the tip, i`ll try it.
pierre
|||Paul,
we have tried to script the references with the "NOT FOR REPLICATION" option
and it caused SQL Server CE to completly break down at replication. After a
quick search on MSDN we found a reported bug saying that SQL Server CE
doesn't support the option mentioned above. (to be specfic SQL Server CE
supports NOT FOR REPLICATION, but there is a bug so we can't use it.)
If I'm not mistaken we have to drop all our references in these
circustances. Am I right? Is there any way to control the order of bulk
inserts at replication. (after PDA downloaded the appropriate snapshot, it
starts to bulk insert & update the rows)
regards
Pierre
"Paul Ibison" wrote:

> Pierre,
> this sounds correct. Actually I misread your original post and didn't notice
> the inner join, so was thinking that the PersonID was a FK directly to the
> books table as an author. You'll need to make sure that the FK relationship
> is 'Not For Replication', as you can't guarantee the replication order (in
> SQL 2005 the default is PK then FK records but not in SQL 2000).
> Regards,
> Paul Ibison
>
>
|||Pierre,
can you post up your reference for this CE bug (I'll put it on my website).
I haven't seen this reference, but if that is the case, you could increase
the -UploadGenerationsPerBatch
and the -DownloadGenerationsPerBatch parameters (to the max of 2000) to
avoid splitting parent and child changes across generation batches.
See http://support.microsoft.com/default...b;EN-US;308266 for more
info.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||BUG: NOT FOR REPLICATION Clause Causes SQL Server CE Replication to Fail
http://support.microsoft.com/default...b;en-us;300597
...however SQL Server CE Books Online says that "NOT FOR REPLICATION" is not
supported in case of foreign key constriants...
anyway, thanks for the tip, i'll try that.
pierre
sql

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnHi John
You could use SET ROWCOUNT 100 before issuing the delete statement. i.e. to
delete in blocks of 100
DECLARE @.earlierstdate datetime
SET @.earlierstdate = getdate()-90
SET ROWCOUNT 100
delete [<table>] where date < @.earlierstdate
WHILE @.@.ROWCOUNT > 0
delete [<table>] where date < @.earlierstdate
SET ROWCOUNT 0
John
"John J. Hughes II" wrote:

> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
>|||John J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much.
I
> was hoping for something like the above but will a limit of say 100 record
s
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.

> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...[vbcol=seagreen]
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>|||Hi John
"John J. Hughes II" wrote:

> John,
> Guess I did not see it before, thanks. By the way the BOL says to use top
> in new development since ROWCOUNT will no longer be supported. If I
> understand correctly then it should be the following:
> delete top(100) [<table>] where data < @.earlierstdate;
>
TOP is only for SQL 2005.

> I was thinking of putting this in a JOB set to when idle Do you think th
is
> would cause too much thrashing or would it be better to put it as you do b
ut
> with a waitfor delay. It would be nice to allow it to run until the serv
er
> became busy and then exit until next idle.
> Something like:
> while(@.@.rowcount > 0 and @.@.cpu < (something)
> delete top(100) [<table>] where data < @.earlierstdate;
> FROM BOL:
> Important:
> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements i
n
> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
> INSERT, and UPDATE statements in new development work, and plan to modify
> applications that currently use it. We recommend that DELETE, INSERT, and
> UPDATE statements that currently are using SET ROWCOUNT be rewritten to us
e
> TOP.
>
How you implement it will depend on how long your quiet periods are and how
many records you are deleting. I would probably start of with a single job
but delting in batches of 5000 (say) and then see if it is an issue. If you
have a policy of only keeping n days then rather than doing that as a monthy
process I would do it more gradually (say daily or weekly).
Make sure you do this deletion before you re-index.
If you do manage to get the system onto SQL 2005 then I would look at using
table partitions.
There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't think
they will be useful to you.
HTH

> regards,
> John
>
John|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>
> TOP is only for SQL 2005.
>
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>
> John
>

Purging old records

I have several table that have basically log records which need to be
purged. The simpliest method is just to do a "delete <table> where date >
getdate()-90". The problem is this really puts a load on the system. I
would like this to be an idle job that does not load the system as much. I
was hoping for something like the above but will a limit of say 100 records
each time, is that possible?
Regards,
JohnJohn J. Hughes II wrote:
> I have several table that have basically log records which need to be
> purged. The simpliest method is just to do a "delete <table> where date >
> getdate()-90". The problem is this really puts a load on the system. I
> would like this to be an idle job that does not load the system as much. I
> was hoping for something like the above but will a limit of say 100 records
> each time, is that possible?
> Regards,
> John
>
Is the "date" column indexed? Is there a DELETE trigger on this table?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45A28AAD.6020608@.realsqlguy.com...
> John J. Hughes II wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> > getdate()-90". The problem is this really puts a load on the system.
>> I would like this to be an idle job that does not load the system as
>> much. I was hoping for something like the above but will a limit of say
>> 100 records each time, is that possible?
>> Regards,
>> John
> Is the "date" column indexed? Is there a DELETE trigger on this table?
Yes the data is indexed and no it is not triggered.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||John,
Guess I did not see it before, thanks. By the way the BOL says to use top
in new development since ROWCOUNT will no longer be supported. If I
understand correctly then it should be the following:
delete top(100) [<table>] where data < @.earlierstdate;
I was thinking of putting this in a JOB set to when idle Do you think this
would cause too much thrashing or would it be better to put it as you do but
with a waitfor delay. It would be nice to allow it to run until the server
became busy and then exit until next idle.
Something like:
while(@.@.rowcount > 0 and @.@.cpu < (something)
delete top(100) [<table>] where data < @.earlierstdate;
FROM BOL:
Important:
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in
the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
INSERT, and UPDATE statements in new development work, and plan to modify
applications that currently use it. We recommend that DELETE, INSERT, and
UPDATE statements that currently are using SET ROWCOUNT be rewritten to use
TOP.
regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BB74E844-32BC-4EC5-9860-F453D1629344@.microsoft.com...
> Hi John
> You could use SET ROWCOUNT 100 before issuing the delete statement. i.e.
> to
> delete in blocks of 100
> DECLARE @.earlierstdate datetime
> SET @.earlierstdate = getdate()-90
> SET ROWCOUNT 100
> delete [<table>] where date < @.earlierstdate
> WHILE @.@.ROWCOUNT > 0
> delete [<table>] where date < @.earlierstdate
> SET ROWCOUNT 0
> John
> "John J. Hughes II" wrote:
>> I have several table that have basically log records which need to be
>> purged. The simpliest method is just to do a "delete <table> where date
>> >
>> getdate()-90". The problem is this really puts a load on the system.
>> I
>> would like this to be an idle job that does not load the system as much.
>> I
>> was hoping for something like the above but will a limit of say 100
>> records
>> each time, is that possible?
>> Regards,
>> John
>>|||John J. Hughes II wrote:
> Yes the data is indexed and no it is not triggered.
>
Take a look at the Estimated Execution Plan for your DELETE statement -
where is it spending the most time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks John...
Did not see where BOL said TOP was new for 2005, guess I will use rowcount
then until SQL 200x ;)
Will more then likely try 1000 at first and there are long periods of idle
time in the mid morning on the system for some reason.
Regards,
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4E8E2FB9-C648-408E-8DF1-8F4BC4278546@.microsoft.com...
> Hi John
> "John J. Hughes II" wrote:
>> John,
>> Guess I did not see it before, thanks. By the way the BOL says to use
>> top
>> in new development since ROWCOUNT will no longer be supported. If I
>> understand correctly then it should be the following:
>> delete top(100) [<table>] where data < @.earlierstdate;
> TOP is only for SQL 2005.
>> I was thinking of putting this in a JOB set to when idle Do you think
>> this
>> would cause too much thrashing or would it be better to put it as you do
>> but
>> with a waitfor delay. It would be nice to allow it to run until the
>> server
>> became busy and then exit until next idle.
>> Something like:
>> while(@.@.rowcount > 0 and @.@.cpu < (something)
>> delete top(100) [<table>] where data < @.earlierstdate;
>> FROM BOL:
>> Important:
>> Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements
>> in
>> the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE,
>> INSERT, and UPDATE statements in new development work, and plan to modify
>> applications that currently use it. We recommend that DELETE, INSERT, and
>> UPDATE statements that currently are using SET ROWCOUNT be rewritten to
>> use
>> TOP.
> How you implement it will depend on how long your quiet periods are and
> how
> many records you are deleting. I would probably start of with a single job
> but delting in batches of 5000 (say) and then see if it is an issue. If
> you
> have a policy of only keeping n days then rather than doing that as a
> monthy
> process I would do it more gradually (say daily or weekly).
> Make sure you do this deletion before you re-index.
> If you do manage to get the system onto SQL 2005 then I would look at
> using
> table partitions.
> There is no @.@.CPU but there is a @.@.CPU_BUSY AND an @.@.IDLE but I don't
> think
> they will be useful to you.
> HTH
>> regards,
>> John
> John
>

Purging a database data while keeping the structure intact

Is it possible to purge all records in the database while retaining the the table structures. Even better yet, could I do it on a table by table basis? If I simply delete all the records the identities for the tables do not revert back to 1.

You can use the TRUNCATE TABLE <tableName> statement, but there are some restrictions (e.g. can't use it on a table referenced by a foreign key, Books Online has the full list of restrictions). If you have to use delete, you can update the identity value with the DBCC CHECKIDENT statement (e.g. DBCC CHECKIDENT ('tableName', RESEED, 0)).

Hope that helps.

Aaron

|||Thanks for your quick respond.

pulling unique records from this query

Hi guys, need your help! (sorry this is quite long)
I've got a table of Projects which I'm using with an asp:Repeater to display
a list of the projects. Here's the sql...
SELECT ProjectID, ProjectName, ProjectClient, DartsContact, LeadArtist,
Projects.AreaOfWork, AreaOfDoncaster, StartDate, EndDate, Running,
WorkAreas.AreaofWork AS AOWName, WorkAreas.RelatesTo AS AOWRelates FROM
Projects, WorkAreas
WHERE (NOT Running=0) AND (Projects.AreaOfWork LIKE '%' + WorkAreas.AOWCode
+ '%') AND (Deleted = 0) ORDER BY ProjectName ASC
As you can hopefully see, I'm using two tables to pull the data together.
It worked fine, until we made a change to the way the data is stored. The
Projects.AreaOfWork field now contains multiple AOWCodes seperated by a
delimiter. So now, whenever I run this query, I get more than 1 line for eac
h
project where there are multiple values in AreaOfWork. So, if I have a
project...
ProjectID, ProjectName, AreaOfWork
1, Test Proj 1, EDU
2, Test Proj 2, EDU|COM
I get 2 lines for Test Project 2, each with a unique AreaOfWork (one with
EDU, one with COM).
2 things... I need to stop it returning multiple records for the same
project when theres more than one AreaOfWork, but I also need to return the
entire AreaOfWork string, because I still need access to those.
Any help would be greatly appreciated.
Cheers
Danwhy have you chosen to store your data like that (delimited) - it
breaks with normalisation, and is the main reason your having problems.
surely it would be easier if you had a separate table like
tblProjWorkAreas(ProjectID, AreaOfWork). Is there are reason for not
doing this?|||I see your point mate. Time constraints are the main reason for this.. it's
an addition to a project that's been running for a couple of years (the
having multiples instead of one).
Is there a way to get the query to work!?
"Will" wrote:

> why have you chosen to store your data like that (delimited) - it
> breaks with normalisation, and is the main reason your having problems.
> surely it would be easier if you had a separate table like
> tblProjWorkAreas(ProjectID, AreaOfWork). Is there are reason for not
> doing this?
>|||actually, further to this... I *think* i can do half of what I want to do in
code, if I can get it to just select unique records... :)
"Will" wrote:

> why have you chosen to store your data like that (delimited) - it
> breaks with normalisation, and is the main reason your having problems.
> surely it would be easier if you had a separate table like
> tblProjWorkAreas(ProjectID, AreaOfWork). Is there are reason for not
> doing this?
>|||depends on what you want out. If we take your example where you have
EDU|COM, what output would you want - the area of work columns x2?
could you post a fuller example in terms of data from both tables, and
what you'd like your query to result in.|||Hi Dan,
Thanks for using MSDN Managed Newsgroup Support.
As Will mentioned, it is not a good idea to store your data like that.
So I want to know why you use the WorkAreas.AreaofWork and
WorkAreas.Relates in the query. If you want to unique the only Project in
this query, I think you may need to exclude the WorkAreas Table.
Also, you may provide me the result of the query now if you have 2
AreaOfWork in the Project Table.
I need to know the exactly different of these 2 records.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you both for your help. I managed to get this to work using a differen
t
method, so no need to concern yourselves anymore :) I do appreciate that
using a third table would be a much better solution, and may consider that
for future redevlopment.
In answer to your question Wei, the reason for showing the AOW fields is
simply to show which Areas of Work a Project belongs to. WorkAreas.Relates i
s
a field that allows us to have inherited Areas of Work in the table, like so
.
aowcode aowname relates_to
1 Education null
2 Adult Ed 1
3 Preschool 1
etc.
Again, thank you both for your help today!
Cheers
Dan
"Wei Lu" wrote:

> Hi Dan,
> Thanks for using MSDN Managed Newsgroup Support.
> As Will mentioned, it is not a good idea to store your data like that.
> So I want to know why you use the WorkAreas.AreaofWork and
> WorkAreas.Relates in the query. If you want to unique the only Project in
> this query, I think you may need to exclude the WorkAreas Table.
> Also, you may provide me the result of the query now if you have 2
> AreaOfWork in the Project Table.
> I need to know the exactly different of these 2 records.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
==========
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>

Pulling data from 2 tables, 1 with possibly multiple records

I'm trying to create a view which pulls data from the following 2 tables:
CREATE TABLE PC (
PCName varchar(50) NOT NULL,
Make varchar(50),
Model varchar(50),
SerialNumber varchar(50)
PRIMARY KEY
(PCName)
)
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
'OptiPlex GX1', '12345')
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
'PowerEdge 6450', '23456')
CREATE TABLE CPU (
PCName varchar(50) NOT NULL REFERENCES PC(PCName),
Row int NOT NULL,
Type varchar(50),
Speed int
PRIMARY KEY
(PCName, Row)
)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
III', 1000)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium III
Xeon', 700)
The CPU table holds an entry for each CPU in the PC. Now I want the view to
retrieve 1 row per PC and look like this...
PCName Make Model SerialNumber Type
Speed NumberOfCPUs
========================================
==============================
TEST1 Dell OptiPlex GX1 12345 Pentium III
1000 1
TEST2 Dell PowerEdge 6450 23456 Pentium III
Xeon 1200 4
... where NumberOfCPUs is the max(Row) for each particular PC.
Any ideas? Unfortunately we are stuck with the table structure as is (from
a 3rd party).
ThanksJim
Thanks for posting DDL
See , if this helps you
SELECT PC.PCName,Make,Model,SerialNumber,
Type, Speed,NumberOfCPUs FROM PC JOIN
(
SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
GROUP BY PCName,Type,Speed
) AS Der ON PC.PCName=Der.PCName
"Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a view which pulls data from the following 2 tables:
> CREATE TABLE PC (
> PCName varchar(50) NOT NULL,
> Make varchar(50),
> Model varchar(50),
> SerialNumber varchar(50)
> PRIMARY KEY
> (PCName)
> )
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
> 'OptiPlex GX1', '12345')
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
> 'PowerEdge 6450', '23456')
> CREATE TABLE CPU (
> PCName varchar(50) NOT NULL REFERENCES PC(PCName),
> Row int NOT NULL,
> Type varchar(50),
> Speed int
> PRIMARY KEY
> (PCName, Row)
> )
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
> III', 1000)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium
> III Xeon', 700)
>
> The CPU table holds an entry for each CPU in the PC. Now I want the view
> to retrieve 1 row per PC and look like this...
> PCName Make Model SerialNumber Type Speed
> NumberOfCPUs
> ========================================
==============================
> TEST1 Dell OptiPlex GX1 12345 Pentium
> III 1000 1
> TEST2 Dell PowerEdge 6450 23456 Pentium III
> Xeon 1200 4
>
> ... where NumberOfCPUs is the max(Row) for each particular PC.
> Any ideas? Unfortunately we are stuck with the table structure as is
> (from a 3rd party).
> Thanks
>|||Yes, that certainly does. Thank you very much.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uMIH0gmvFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Jim
> Thanks for posting DDL
> See , if this helps you
>
> SELECT PC.PCName,Make,Model,SerialNumber,
> Type, Speed,NumberOfCPUs FROM PC JOIN
> (
> SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
> GROUP BY PCName,Type,Speed
> ) AS Der ON PC.PCName=Der.PCName
>
> "Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
> news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>