Max and Joins 1

[itflash]

Hi All

I am struggling with a query.

I have 3 tables:

Job Key: jobNumber (int).

Stage Key: StageID (int)
Foreign Keys: JobNumber (int), TypeID (int)

Type Key: TypeID (int)
Other: DisplayName (varchar)


Each Job has various stages in the Stage Table and each stage has one type. The Type is used to determine the stage order.

What I want is to display each job and show the MAX(typeid) for each job along with its display name.
Dont want to display the stages, only one line per job.

I can quite easily get it to work OK, but just cannot figure out how to return the DISPLAYNAME as well!!


select job.jobnumber, max(stage.typeid)
FROM job
LEFT JOIN stage ON stage.jobnumber=job.jobnumber
LEFT JOIN type ON type.typeid=stage.typeid
GROUP BY job.jobnumber

How can I include the display name?


Thanks
ITflash

 

[swampBoogie]

select j.jobnumber, s.typeid, t.displayName
  FROM job j
  JOIN stage s ON s.jobnumber=j.jobnumber
  JOIN type t ON t.typeid = s.typeid
 where typeid in (select max(typeid) from stage
 where jobnumber = s.jobnumber)

 

[itflash]

Thanks, that works great,

However, only one draw back.....

If my job has no stages yet, then it is not brought back in the query. Hence why I was using LEFT JOINs.

Any ideas?

Thanks
ITflash

 

[swampBoogie]

select j.jobnumber, s.typeid, t.displayName
  FROM job j
  JOIN stage s ON s.jobnumber=j.jobnumber
  JOIN type t ON t.typeid = s.typeid
 where typeid in (select max(typeid) from stage
 where jobnumber = s.jobnumber)
union all select j.jobnumber, 0,''
from job j
where jobnumber not in (
select jobnumber from stage)

 

[itflash]

Nice One

Many Thanks and a Vote.


ITflash