CODEWITHSUNDEEP
sqlsql-serverdatabasejoin
Axwack
Axwack

Reputation: 569

How do I do a look up from a user table where the user can be in two columns of the record?

This is a very simple idea and want to validate my approach.

I have a record that has the following:

RESERVATIONS

ID OWNER RESIDENT
1 VLL MLL
2 MLL CVLL

The lookup table looks like this:

USER_TABLE

PLID USER_CD BRANCH
1a VLL USA
2a MLL UK

I want to look up the value in the second table for owner and resident. You cannot join the ID's together because they are not related. The only relation is from owner to USER_CD

Currently, I do the following:

Select CASE
  when reservations.owner = 'VLL' then 1.user_cd
  when reservations.owner = 'MLL' then 2.user_cd
end as 'Location'

FROM RESERVATIONS r
Join USER_TABLE 1
on RESERVATIONS.OWNER = 1.USER_CD
join USER_TABLE 2
ON RESERVATIONS.RESIDENT = 2.USER_CD

Is this a correct way to do it or is there another way?

Here is an example implementation: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=92f6178194a7e67f15652cbe7cc549c1

Upvotes: 0

Views: 40

Answers (1)

MatBailie
MatBailie

Reputation: 86765

First, don't use numerals as table aliases. That's even worse than using arbitrary letters. The aliases should still Mean something to anyone reading or debugging the code.

Second, if you want to translate short branch names to long brnach names, the best way is to have another table with those lookups. In the code below I create that as an inline view (sub-query), though a real table with indexes would be Significantly better.

Then, I believe you're very close already...

WITH
  branch_long_name(
    name,
    long_name
  )
AS
(
            SELECT 'USA', 'United States'
  UNION ALL SELECT 'UK',  'United Kingdom'
  -- Note; the UK and Great Britain are not the same
),
  branch
AS
(
  SELECT
    t.*,
    COALESCE(n.long_name, t.branch)   AS long_name
  FROM
    user_table   AS t
  LEFT JOIN
    branch_long_name   AS n
      ON n.name = t.branch
)
SELECT
  r.*,
  b_o.long_name   AS owner_location,
  b_r.long_name   AS resident_location
FROM
  reservations   AS r
LEFT JOIN
  branch   AS b_o   -- branch_owner
    ON b_o.user_cd = r.owner
LEFT JOIN
  branch   AS b_r   -- branch_resident
    ON b_r.user_cd = r.resident

Demo : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7cc940ddf40e7cd085cff0fa02b65449

Upvotes: 1

Related Questions