MS sql server Problem

[obarash]

Hello My name is Oren and I'm new here,

I have a problem and i'm trying to solve it for a couple of days.

I have a table Called WORKERS and inside a column named Projects And in this column i have values like "1,5,7,12,1 5" (ID)

And i need to get all the Wrokers details that are connected to some project ID supose 12,

And then bring all the projects names that this worker was part of.

for example:
supose the project i'm searching is 12
I have a worker record:

Fname Lname Age Projects
Tony Mitz 25 1,5,7,12,15

The resault should be:

Tony Mits 25
Projects:
projectName1, projectName2 , projectName3 ,projectName4 ,projectName 5

How to Solve this??

I hope somebody will help me,

Thanks

[radcaesar]

Make a project details in to a seperate table and wworker details in another table. Relate it with the project ID (Foreign Key)

Then get the values like

select * from workers w, projects p where w.id=p.id

got it ?

:)

Hello My name is Oren and I'm new here,

I have a problem and i'm trying to solve it for a couple of days.

I have a table Called WORKERS and inside a column named Projects And in this column i have values like "1,5,7,12,1 5" (ID)

And i need to get all the Wrokers details that are connected to some project ID supose 12,

And then bring all the projects names that this worker was part of.

for example:
supose the project i'm searching is 12
I have a worker record:

Fname Lname Age Projects
Tony Mitz 25 1,5,7,12,15

The resault should be:

Tony Mits 25
Projects:
projectName1, projectName2 , projectName3 ,projectName4 ,projectName 5

How to Solve this??

I hope somebody will help me,

Thanks

[obarash]

got It but it's a little bit tricky,
because every worker is connected to several prjects, and every project is connected to several workers, so in your example ( or answer) i will have many many records and quite a mess in the db.

let me explain myself again:

a worker have a field named Projects and it's values are : "5,7,12,16"

hope now it is more clarified

Or mybe I didn't understand you

Make a project details in to a seperate table and wworker details in another table. Relate it with the project ID (Foreign Key)

Then get the values like

select * from workers w, projects p where w.id=p.id

got it ?

:)

[Motoma]

You make it sound like you have a Workers table and a Projects table. I am working off this assumption.

What you will most likely want to do, is create a third table, I'll call it w_p_rel, which will hold a worker id wID, and a project id pID.

This table will contain the many to many relationship of projects to workers. For ever project/worker combination, there will be one entry in the w_p_rel table.

When you need to get all of the projects a worker is part of:

SELECT project.*
FROM project, w_p_rel 
WHERE w_p_rel.wID = <workerID>
AND w_p_rel.pID = project.ID

And when you need to get all of the workers for a specific project:

SELECT workers.*
FROM workers, w_p_rel 
WHERE w_p_rel.pID = <projectID>
AND w_p_rel.wID = worker.ID

Hope this helps,
Motoma

[obarash]

Thank you,

but there isn't way to solve without changing the db?

You make it sound like you have a Workers table and a Projects table. I am working off this assumption.

What you will most likely want to do, is create a third table, I'll call it w_p_rel, which will hold a worker id wID, and a project id pID.

This table will contain the many to many relationship of projects to workers. For ever project/worker combination, there will be one entry in the w_p_rel table.

When you need to get all of the projects a worker is part of:

SELECT project.*
FROM project, w_p_rel 
WHERE w_p_rel.wID = <workerID>
AND w_p_rel.pID = project.ID

And when you need to get all of the workers for a specific project:

SELECT workers.*
FROM workers, w_p_rel 
WHERE w_p_rel.pID = <projectID>
AND w_p_rel.wID = worker.ID

Hope this helps,
Motoma

[almaz]

Thank you,

but there isn't way to solve without changing the db?

We can provide tricky SQL statements that may solve your current problem, but the main problem is a design problem.Current design doesn't confirm even to the first normal form. You described a simple many-to-many relationship between workers and projects, and it has a standard solution as Motoma described.
The best choice here is to review the DB structure, because later there may occur another challenges like "when particular project is deleted, delete all references to it" and so on.

[Motoma]

We can provide tricky SQL statements that may solve your current problem, but the main problem is a design problem.Current design doesn't confirm even to the first normal form. You described a simple many-to-many relationship between workers and projects, and it has a standard solution as Motoma described.
The best choice here is to review the DB structure, because later there may occur another challenges like "when particular project is deleted, delete all references to it" and so on.

Thanks for your support, as well as the comment on normalization. I completely forgot to mention this.

A great place to read up on this is on this UTexas page.
A particularly good page to read is the one on Normalization as it provides numerous examples of both the how and the why of normalization.
This page talks about the process of resolving Many-to-Many relationships.

[iburyak]

If you still insist on doing select your way try this:

1. Create a function:

[PHP]CREATE FUNCTION SplitProjects(@ String nvarchar(4000), @Delimiter char(1))
RETURNS varchar(4000)
AS

BEGIN
DECLARE @Results varchar(4000)
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)

SELECT @INDEX = 1, @Results = ''
WHILE @INDEX !=0


BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Deli miter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@I NDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS
SELECT @Results = @Results + (select project from projects where id = @SLICE) + ','

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,L EN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
-- Remove last comma
Select @Results = SUBSTRING(@Resu lts,1,len(@Resu lts) - 1)
RETURN @Results
END [/PHP]

2. Execute select with function:

[PHP]select *, dbo.SplitProjec ts(Projects,',' ) from Workers[/PHP]

[obarash]

thank you,
I will try to get an access to the db, I'm not allowed to do changes so this is why i asked my question.

but now I see that I right and so are you.
the designing from the begining is defected.

thank you.

If you still insist on doing select your way try this:

1. Create a function:

[PHP]CREATE FUNCTION SplitProjects(@ String nvarchar(4000), @Delimiter char(1))
RETURNS varchar(4000)
AS

BEGIN
DECLARE @Results varchar(4000)
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)

SELECT @INDEX = 1, @Results = ''
WHILE @INDEX !=0


BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Deli miter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@I NDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS
SELECT @Results = @Results + (select project from projects where id = @SLICE) + ','

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,L EN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
-- Remove last comma
Select @Results = SUBSTRING(@Resu lts,1,len(@Resu lts) - 1)
RETURN @Results
END [/PHP]

2. Execute select with function:

[PHP]select *, dbo.SplitProjec ts(Projects,',' ) from Workers[/PHP]

[ajitjacob]

hi,
here is a tricky SQL i am not sure it will give correct result set always, but u can use it for ur problem.

Select * from Workers inner join Projects
ON ProjectIds LIKE '%'+CAST(Projec tId AS VARCHAR)+'%'
where ProjectId = 12

it would be better if u can save the projectIds starting and ending with zeros like
0,1,4,2,12,0

but any way its better to change the DB structure