SQL: only return results, after a join, where columns with different words represent the same idea (-CodePudding

SQL flavor is Mode's unique variation, that said SQL flavor doesn't really matter. If you can show me how to do it in one variety of SQL, I'll at least know what to Google to figure it out in this variation.

I'm joining two tables and trying to identify columns where the status of an item is the same, however the statuses are written differently between the two tables.

Table 1 columns:

Name
Number
Status (available, unavailable, inactive)

Table 2 columns:

Number
Status (unassigned, unavailable, retired)

Available = unassigned, unavailable = unavailable, inactive = retired.

I am trying to first compare available/unassigned line up, inactive/retired line up, etc. Then I'm trying to return only the results where both status columns do not match, but since they use different words for the same idea I just don't know how to do it.

CodePudding user response：

I'd simply recode the Status values to numeric code in a WHERE clause like so

SELECT t1.*,t2.Status FROM t1
LEFT JOIN t2 ON t1.Number = t2.Number 
WHERE CASE WHEN t1.Status = 'available' THEN 1
           WHEN t1.Status = 'unavailable' THEN 2
           WHEN t1.Status = 'inactive' THEN 3
      END != CASE WHEN t2.Status = 'unassigned' THEN 1
           WHEN t2.Status = 'unavailable' THEN 2
           WHEN t2.Status = 'retired' THEN 3
      END

http://sqlfiddle.com/#!9/39f243/2

CodePudding user response：

You may use something like the below:

; With StatusMapping AS 
(
    SElect 'Available' T1Status , 'Unassigned' T2Status
    UNION 
    SELECT 'Unavailable', 'Unavailable' 
    UNION 
    SELECT 'Inactive', 'Retired' 
)
SELECT * 
FROM T1
INNER JOIN StatusMapping M ON M.T1Status = T1.Status
INNER JOIN T2 
ON T1.Number = T2.Number 
AND T2.Status = M.T2Status