Page 1 of 1

sql table search / php sort

Posted: Fri Sep 19, 2008 11:07 am
by Weasel5-12
G'day guys.. just a quick q.

I've got the following table below with taskID and staff as an example.
The ID has a 1:M relationship with it..

My q. is.. how can i search these to find duplicate staff (all staff from one task) working on a 2nd task ( again, all staff on the same task) ??
example. task 1 & 4 have the same staff workin on 2 seperate projects..

I dont have any code to show, because it's all been so useless ive scrapped it...

ID | Staff
1 | bob
1 | sally
1 | gary
2 | gary
2 | michelle
2 | rick
2 | justin
3 | bob
3 | sally
3 | rick
4 | bob
4 | sally
4 | gary

can some1 please help??? this happens to be a crutial part of my project.. LOL

thankyou VERY much in advance to any1 who replys

Re: sql table search / php sort

Posted: Fri Sep 19, 2008 3:20 pm
by yacahuma
how do you want the results

you could have
person, count(how many tasks)

how to do you the output?

Re: sql table search / php sort

Posted: Fri Sep 19, 2008 4:57 pm
by Weasel5-12
I'm looking to remove this from the tables honestly.

I dont want to have the same team on the same task.

So... im looking for a comparison. If im adding a new team to the database for a new task, i want to make sure that those members havn't worked with each other ... if i make sense :?:

I've got my database set up atm, and i've backed it up, and created a new db. As im now assignming members to tasks, i want to make sure that they havn't worked with each other previously.

And if they have, all i need is a simple error msg.. and if they havn't.. add them to the db


cheerz mate

Re: sql table search / php sort

Posted: Fri Sep 19, 2008 9:08 pm
by yacahuma
this tells me all the people gary works with in the past

select distinct staff from groups where id in (select id from groups where staff ='gary') and staff <> 'gary')

so If I have a new task(id) and I want to know if there are people gary worked in the past

select count(staff) from groups where id =7 and staff in(
select distinct staff from groups where id in (select id from groups where staff ='gary') and staff <> 'gary')
if is zero then you can safely add gary to id 7