SQL Statement with 2 WHERE for same table

[blasterstudios]

I have a query setup that pulls values from other tables based on their value in that referenced column. However 2 column values will come from the same table.

I use a table USERS that has reps and designers in it. I would like reference both in my query but I'm not sure how to discern from 2. Here's my query:

SELECT
     fox_clients.code AS code,
     fox_jobs.jobid AS jobid,
     fox_clients.name AS client,
     fox_users.firstname AS rep,
     fox_jobs.designer,
     fox_jobs.duedate,
     fox_status.status AS status,
     fox_priorities.prioritylevel AS priority,
     fox_types.type AS jobtype
FROM
     fox_jobs,
     fox_clients,
     fox_users,
     fox_status,
     fox_priorities,
     fox_types
WHERE
     fox_clients.clientid = fox_jobs.client AND
     fox_users.userid = fox_jobs.rep AND
     fox_status.statusid = fox_jobs.status AND
     fox_priorities.priorityid = fox_jobs.priority AND
     fox_types.typeid = fox_jobs.jobtype

The problem occurs in my where, because on the 2nd line, it says fox_users.userid = fox_jobs.rep, but i also want it to pull the rows of fox_jobs.designer as well. I'm just not sure how to say, this is for the field rep, and this is for the field designer. Everything else works perfect.

 

[hos2]

SELECT
     fox_clients.code AS code,
     fox_jobs.jobid AS jobid,
     fox_clients.name AS client,
     fox_users.firstname AS rep,
     fox_users_designer.firstname as design
     fox_jobs.duedate,
     fox_status.status AS status,
     fox_priorities.prioritylevel AS priority,
     fox_types.type AS jobtype
FROM
     fox_jobs,
     fox_clients,
     fox_users,
     fox_users as fox_users_designer,
     fox_status,
     fox_priorities,
     fox_types
WHERE
     fox_clients.clientid = fox_jobs.client AND
     fox_users.userid = fox_jobs.rep AND
     fox_users_designer.userid = fox_jobs.designer AND
     fox_status.statusid = fox_jobs.status AND
     fox_priorities.priorityid = fox_jobs.priority AND
     fox_types.typeid = fox_jobs.jobtype

something like this, create an alias for you're user table.

but these kind of questions are more for the mysql forum

 

[blasterstudios]

this doesn't seem to work. it pulls the value for the rep, so it shows up as the rep and designer are the same.

 

[hos2]

there are some syntax errors I see, I forgot a , and referered to designer instead of design

 

[Vragabond]

AFAIK you won't be able to do this with natural joins (WHERE) so you will have to resort to JOINs. This is however better suited question for the MySQL forum (forum436). In case you want to do some reading on JOINs, here's the reference from MySQL manual:

http://dev.mysql.com/doc/mysql/en/join.html

 

[blasterstudios]

oh, man, i'm sorry, i think what you gave me did work. I had one small thing wrong with it. this is what i have now, and i believe it is working:

SELECT
    fox_clients.code AS code,
    fox_jobs.jobid AS jobid,
    fox_clients.name AS client,
    fox_users_rep.firstname AS rep,
    fox_users_designer.firstname AS designer,
    fox_jobs.duedate,
    fox_status.status AS status,
    fox_priorities.prioritylevel AS priority,
    fox_types.type AS jobtype
FROM
    fox_jobs,
    fox_clients,
    fox_users AS fox_users_rep,
    fox_users AS fox_users_designer,
    fox_status,
    fox_priorities,
    fox_types
WHERE
    fox_clients.clientid = fox_jobs.client AND
    fox_users_rep.userid = fox_jobs.rep AND
    fox_users_designer.userid = fox_jobs.designer AND
    fox_status.statusid = fox_jobs.status AND
    fox_priorities.priorityid = fox_jobs.priority AND
    fox_types.typeid = fox_jobs.jobtype
ORDER BY
    fox_jobs.duedate ASC,
    fox_jobs.priority DESC

Thanks man, and sorry about the hard time i gave you in the other thread. You can't always understand a person's "tone of voice" over the web.