Topic: selecting records with join

[redneonrt]

I have 3 tables im working with

sab_merc (job table)
-id
-username
-service
-job_desc

Users (user table)
-id
-username
-other misc info

onjob_sab  (theis in the x-ref table in a many to many relationship)
-user_id
-job_id

Code: [Select]

select  from onjob_sab left join sab_merc on(onjob_sab.user_id = sab_merc.id) where (onjob_sab.user_id != '1')
what I get from this query is all the open jobs that user 1 is not part of.

Now since multiple people can can be on multiple jobs I get a query that repeats the jobs for each users on them

Code: [Select]

2     chris     sextreme69     AIM     Stryker_     http://www.kingsofchaos.com/stats.php?id=3012255     Elves     1     Gold     500000     0           11.13.05     1131940811     open     2     1
2     chris     sextreme69     AIM     Stryker_     http://www.kingsofchaos.com/stats.php?id=3012255     Elves     1     Gold     500000     0           11.13.05     1131940811     open     2     3
4     workwarnings     testee     MSN     Steeringwhell     http://www.kingsofchaos.com/stats.php?id=3009074     Elves     5     Gold     78552     0           11.15.05     1132116959     open     4     4
4     workwarnings     testee     MSN     Steeringwhell     http://www.kingsofchaos.com/stats.php?id=3009074     Elves     5     Gold     78552     0           11.15.05     1132116959     open     4     5

All I really want from this query is to fin all jobs that user 1 is not assiged too and them display them in a "while loop" using php.

So in the example you can see that there is really only 2 jobs available that user 1 is not assiged too, but they get repeated since there is already other users on those jobs.

Hope this makes sense

Thanks in advance,
Chris