query on a query

I need a help with a small query.

Table A:- status and the contents of it are below.

 status_id      status_descrip 
        1          held 
        2          release 
        3          WIP

Table B:- Entry is another table which has some 30 fields. Out of which 2 fields are referring to Table A-> status table. For simplicity i am skipping other fields and jotting down only the fields related with status.

 entry_id        design_status         stress_status
     1                 3                   1
     2                 1                   2
     3                 NULL                2
     4                 3                   3
     5                 NULL                1
     6                 NULL               NULL      
     7                 NULL                2   

I can't change the structure of Entry to split into 2 tables one as design_status and other as stress_status.

Now i want the entries from entry table replace the status with their descriptions. so the output will be

   entry_id        design_status_descrip         stress_status_descrip
      1                   WIP                          held
      2                   held                         release
      3                   NULL                         release
      4                   WIP                          WIP.
      5                   NULL                      开发者_运维问答   held
      6                   NULL                         NULL
      7                   NULL                         release

Thanks in advance!

EDIT:-

Question is edited with Entries.

Thankyou for all your responses!

and the query finally working for me is,

SELECT     b.entry_id,     design_status_descrip = d.status_descrip,   
  stress_status_descrip = s.status_descrip FROM     entry AS b   
  LEFT OUTER JOIN status AS d     ON b.design_status = d.status_id   
  LEFT OUTER JOIN  status AS s     ON b.stress_status = s.status_id

do let me know, if i can improve this query.

---

The simplest way would be to join against the status table twice. I've used INNER JOIN assuming that the design_status and stress_status columns in your schema have proper constraints. Edited for changed requirements.

DECLARE @status TABLE
(
    status_id INT PRIMARY KEY,
    status_descrip VARCHAR(32) NOT NULL UNIQUE
);

INSERT @status SELECT 1,'held' UNION SELECT 2,'release' UNION SELECT 3,'WIP';

DECLARE @b TABLE
(
    entry_id INT PRIMARY KEY,
    design_status INT,
    stress_status INT
);

INSERT @b SELECT 1,3,1
UNION SELECT 2,1,2
UNION SELECT 3,NULL,2
UNION SELECT 4,3,3
UNION SELECT 5,NULL,1
UNION SELECT 6,NULL,NULL
UNION SELECT 7,NULL,2;

SELECT
    b.entry_id,
    design_status_descrip = d.status_descrip,
    stress_status_descrip = s.status_descrip
FROM
    @b AS b
    LEFT OUTER JOIN @status AS d
    ON b.design_status = d.status_id
    LEFT OUTER JOIN @status AS s
    ON b.stress_status = s.status_id;

---

just join twice to the status table

From
      Entry e 
     Inner join status ds
    On e. design_status_id = ds.status_id 
     Inner join status ss
    On e.stress_status_id = ss.status_id

---

SELECT 
    e.entry_id, 
    s1.status_descrip as design_status_descrip, 
    s2.status_descrip as stress_status_descrip 
FROM entry e, status s1, status s2 
WHERE 
    e.design_status = s1.status_id 
    AND e.stress_status = s2.status_id

---

Couple of approaches, first is the most efficient.

SELECT
    e.entry_id
    , MAX(CASE WHEN e.design_status = s.status_id THEN s.status_descrip END) AS design_status_descrip
    , MAX(CASE WHEN e.stress_status = s.status_id THEN s.status_descrip END) AS stress_status_descrip
FROM
    dbo.[Entry] e 
LEFT OUTER JOIN
    dbo.[Status] s 
ON  s.status_id = e.design_status
OR  s.status_id = e.stress_status
GROUP BY
    entry_id


SELECT
    e.entry_id
    , ds.status_descrip AS design_status_descrip
    , ss.status_descrip AS stress_status_descrip
FROM
    dbo.[Entry] e 
LEFT OUTER JOIN
    dbo.[status] ds
ON  ds.status_id = e.design_status
LEFT OUTER JOIN
    dbo.[status] ss
ON  ss.status_id = e.stress_status

---

I notice you have NULL's in your data, in which case ignore all the answers using INNER JOIN as this will effectively filter out any row with a null in either column. Instead use a left join.

SELECT
   e.entry_id,
   ds.status_description,
   ss.ststua_description
FROM entry e
LEFT JOIN status ds 
  ON e.design_status = ds.status_id
LEFT JOIN status ss 
  ON e.stress_status = ss.status_id