SQL - How to JOIN with case when-CodePudding

Is this the correct way to join a table to a different table based on type/value?

In my table, I have a column geo which can be 1, 2, or 3 and geo_id. Based on geo's type I want to join another table. The only solution if found so far is using union.

  SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    cntr.countryName as geo_name
FROM
    tbl_documents doc
JOIN 
    tbl_countries cntr ON doc.geo_id = cntr.country_Id
WHERE doc.geo = 1

UNION ALL

SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    reg.regionName as geo_name
FROM
    tbl_documents doc
JOIN 
    tbl_regions reg ON doc.geo_id = reg.region_Id
WHERE 
    doc.geo = 2

UNION ALL

SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    area.areaName as geo_name
FROM
    tbl_documents doc
JOIN 
    tbl_areas area ON doc.geo_id = area.area_Id
WHERE doc.geo = 3
ORDER BY document_id

CodePudding user response：

You can also LEFT JOIN:

SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    coalesce(cntr.countryName, reg.regionName, area.areaName) as geo_name
FROM
    tbl_documents doc
LEFT JOIN 
    tbl_countries cntr ON doc.geo_id = cntr.country_Id AND doc.geo = 1
LEFT JOIN 
    tbl_regions reg ON doc.geo_id = reg.region_Id AND doc.geo = 2
LEFT JOIN 
    tbl_areas area ON doc.geo_id = area.area_Id AND doc.geo = 3
ORDER BY doc.document_id

CodePudding user response：

Left join each of the geo tables and then use COALESCE to select the first not null value from the geo tables:

  SELECT 
    doc.document_id,
    doc.documentTitle,
    doc.documentUrl,
    doc.created_at,
    doc.delete_date,
    COALESCE(cntr.countryName, reg.regionName, area.areaName) AS geo_name
FROM
    tbl_documents doc
LEFT JOIN 
    tbl_countries cntr 
    ON doc.geo_id = cntr.country_Id
    AND doc.geo = 1
LEFT JOIN 
    tbl_regions reg 
    ON doc.geo_id = reg.region_Id
    AND doc.geo = 2
LEFT JOIN 
    tbl_areas area 
    ON doc.geo_id = area.area_Id
    AND doc.geo = 3

CodePudding user response：

UNION the tables into a single source to join on, adding in an additional column to specify the source/geo_type and include in your join.

It's effectively the same as unioning three queries, as you've done, but reducing the amount of code repetition.

SELECT 
  doc.document_id,
  doc.documentTitle,
  doc.documentUrl,
  doc.created_at,
  doc.delete_date,
  label.name as geo_name
FROM
  tbl_documents   AS doc
LEFT JOIN
(
  SELECT 1 as geo, country_id AS id, countryName AS name FROM tbl_countries
  UNION ALL
  SELECT 2 as geo, region_id  AS id, regionName  AS name FROM tbl_regions
  UNION ALL
  SELECT 3 as geo, area_id    AS id, areaName    AS name FROM tbl_areas
)
  AS label
    ON  label.geo = doc.geo
    AND label.id  = doc.geo_id
ORDER BY
  doc.document_id