Joins question

[galorin]

I am using the following query to grab records to generate a unique identifier.

SELECT House_type, softver, builder_id, level, Floor, Revision, dist_id, enq_id
FROM `contacts`.`HouseType` where quote_id = 2203

builder_id, dist_id and enq_id are all numeric fields. I have another table, called Short. It connects the numeric fields to abbreviated company names. It looks like this:

-------------------
|Comp_ID|ShortCode|
|-------|---------|
|  1    |  aaa    |
|  2    |  acb    |
|  3    |  afw    |
-------------------

A third table contains company names to a numeric company ID, so HouseType.builder_id = Short.CompID = company.CompID

How do I change the query so that it will return the ShortCode for builder_id, dist_id and enq_id instead of just one?

 

[r937]

SELECT h.House_type
     , h.softver
     , b.ShortCode AS builder_ShortCode
     , h.level
     , h.Floor
     , h.Revision
     , d.ShortCode AS dist_ShortCode
     , e.ShortCode AS enq_ShortCode
  FROM `contacts`.`HouseType` AS h
INNER
  JOIN Short as b
    ON b.Comp_ID = h.builder_id
INNER
  JOIN Short as d
    ON d.Comp_ID = h.dist_id
INNER
  JOIN Short as e
    ON e.Comp_ID = h.enq_id
 WHERE h.quote_id = 2203

r937.com | rudy.ca

 

[galorin]

Ahh,I wondered how aliases worked,thanks