Jump to content


Photo

selecting records with join


  • Please log in to reply
No replies to this topic

#1 redneonrt

redneonrt
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 16 November 2005 - 05:05 PM

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


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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users