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
.
>

Publishing to a remote SSL server

I've set up SQL Reports in 'https://www.projects.com/reports' and added a
reports project to a solution. I used the reports manager to add a folder in
which to deploy the reports, but can't figure out where it is on the server.
When I try to deploy a report to 'https://www.projects.com/reportserver' I
get the following error: 'A connection can not be made to the report server
https://www.projects.com/ReportServer'. Any help to do the following:
1. Set up a folder on the server to deploy the datasource and reports so I
can xcopy
2. Be able to make a connection when I attempt to deploy a report from
VS.NET.
Would be appreciated.
Thanks.
DanI resolved this problem by first installing the data source then the report.
The report runs on the local server, but any attempt to run the report or
sample reports over the internet gives the error: 'Cannot find server or DNS
Error'. I'm publishing behind ISA 2004. Any suggestions?
Thanks.
Dan
"Dan Slaby" <dls@.newsgroup.nospam> wrote in message
news:ucM%23rdOjFHA.3568@.tk2msftngp13.phx.gbl...
> I've set up SQL Reports in 'https://www.projects.com/reports' and added a
> reports project to a solution. I used the reports manager to add a folder
> in which to deploy the reports, but can't figure out where it is on the
> server. When I try to deploy a report to
> 'https://www.projects.com/reportserver' I get the following error: 'A
> connection can not be made to the report server
> https://www.projects.com/ReportServer'. Any help to do the following:
> 1. Set up a folder on the server to deploy the datasource and reports so I
> can xcopy
> 2. Be able to make a connection when I attempt to deploy a report from
> VS.NET.
> Would be appreciated.
> Thanks.
> Dan
>|||Hello,
I'd like to confirm that both report server and SQL server are behind ISA
2004.
Please note that Windows authentication is not supported via Internet
because Kerbose/NTLM authentication need open too many ports via firewall
which is not proper for security purpose.
If you want to run the report over the internet, you may need to enable
anonymous access. You need to configure via report manager to enable
Iusr_<computername> account to have the proper permission to the proper
roles such as browser/content manager etc.
I suggest that you perform the following steps to narrow down the issue:
1. Run the report internally from another client computer with anonymous
access.
If the user can run the report internally successfully with anonymous
access, you may need to check the configuration of the ISA server.
To enable Anonymous Access to work with SQL Server Reporting Services,
enable Anonymous Access under Directory Security in IIS and set the default
account to IWAM_machinename
NOTE: By default SQL Server Reporting Services uses the IWAM_machinename
account to connect.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.