MYSQL Query Help

Databases
1

Guys I have a couple tables in the following form:

jobs
±---------------------------------------------+
|id |work |idStartSalesman |idEndSalesman|
±---------------------------------------------+
| 1 |info | 2 | 2 |
| 2 |info2 | 1 | 2 |
| 3 |info3 | 2 | 3 |
±---------------------------------------------+

salesman
±------------------+
| id | salesName |
±------------------+
| 1 | Bob |
| 2 | Tim |
| 3 | Ron |
±------------------+

I’m having problems with the query. I need to display both salesman, but I’m having problems visualizing how I do this. I can get the one obviously buy using the following:

"SELECT jobs.work, salesman.salesName FROM jobs LEFT JOIN salesman ON ‘jobs.idStartSalesman’ = ‘salesman.id’

How do I get 3 columns to display work, salesName(start), salesName(end). Any help would be appreciated.

2

Do a second join to salesman but give it a different alias.


select  a.work, b.salesName,  c.salesName
from jobs a LEFT OUTER JOIN salesman b
 on  a.idstartsalesman = b.id
LEFT OUTER JOIN salesman c
  on a.idEndSalesman =  c.id
3

i would use slightly more meaningful aliases than “a, b, c”

also, the LEFT OUER JOINs should really be INNER JOINs, unless either idStartSalesman or idEndSalesman is optional, i.e. can be NULL

the given examples, however, have both values, so maybe “start” and “end” salesman are both mandatory

4

Thanks for the replies guys. Actually both salesmen can be NULL. When a new project gets created it may be unassigned for a short period. What will happen next is it will be assigned to someone and when the project is complete the endSalesman will be filled out.

I’m sorry guys, but can you explain alias to me? I thought it had to be in the form tablename.columnname. Using an alias, how does it know where to look because you’re not providing a real table name?

5

an alias is just another name for a table or colulmn

example:

SELECT j.work AS job FROM jobs AS j

table alias is j, column alias is job

6 

SELECT
	 j.id
	 ,j.work
     ,s1.id salesman_start_id
     ,s1.salesName salesman_start_name
     ,s2.id salesman_end_id
     ,s2.salesName salesman_end_name
  FROM
     jobs j
  LEFT OUTER
  JOIN
     salesman s1
    ON
     j.idStartSalesman = s1.id
  LEFT OUTER
  JOIN
     salesman s2
    ON
     j.idEndSalesman = s2.id