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
.
>
atabase Password='%s';SSCE:Encrypt Database='true';SSCE:Max Database Size=384;SSCE:Temp File Max size=384;SSCE:Temp File Directory=%s"