query

Imagine we have 2 tables
personal table with 3 fields:
personalid int
jobid int
oldjobid int

and table job with 2 fields :
jobid int
jobname varchar

now I want write a query with this result :
personalid,jobname,oldjobname
which jobname means the name in job table related with jobid in personal table
and oldjobname means the name in job table related with oldjobid in personal table
please help me

SELECT
personalid,
jobname,
(SELECT jobname FROM job INNER JOIN personal on personal.oldjobid=job.jobid) AS Oldjobname
FROM
personal
INNER JOIN job ON personal.jobid = job.jobid
|||

tanks for you answer
but when I run this query this error occure:
"subquery returned more than 1 value. This is not permitted when the subquery follows =,!=,<,<=,<,<= or when the subquery is used as an expression"

|||Try this:
SELECT personalid, j1.jobName, j2.jobName as oldJobName
FROM personal p
INNER JOIN job j1 ON p.jobID = j1.jobID
INNER JOIN job j2 ON p.oldJobID = j2.jobID

Nick|||

tank you

exactly true