Join the same table (multiple times) on same column to different tables-CodePudding

I have looked everywhere but I am not able to find an efficient manner to join a table to multiple tables on the same column. Let's say, I have tables like the below:

employee

emp_id | emp_name | gender_id | age_range_id
----------------------------------------------
101    | adam     | 1         | 3
102    | ashley   | 2         | 2
103    | Cody     | 1         | 4

visitor

visitor_id | visitor_name | gender_id | emp_id | age_range_id
---------------------------------------------------------------
501        | john         | 1         | 101    | 3
502        | lily         | 2         | 101    | null
503        | jeff         | 1         | 102    | 2

gender

gender_id | gender_name
--------------------------
1         | male
2         | female

age_range

age_range_id | age_range_name
------------------------------
1            | 18-25
2            | 26-35     
3            | 36-45
4            | 46-55
5            | 56-65

What I want:

emp_id | emp_name | emp_gender_and_age_range | visitor_name | visitor_gender_and_age_range
---------------------------------------------------------------------------------------------------
101    | adam     | male 36-45               | john         | male 36-45
101    | adam     | male 36-45               | lily         | female       
102    | ashley   | female 26-35             | jeff         | male 26-35       
103    | Cody     | male 46-55               | null         | null

My code:

SELECT e.emp_id
      ,e.emp_name
      ,g1.gender_name || ' ' || a1.age_range_name emp_gender_and_age_range
      ,v.visitor_name
      ,g2.gender_name || ' ' || a2.age_range_name emp_gender_and_age_range
  FROM employee e
       LEFT JOIN gender g1 ON e.gender_id = g1.gender_id
       LEFT JOIN age_range a1 ON e.age_range_id = a1.age_range_id
       LEFT JOIN visitor v ON e.emp_id = v.emp_id
       LEFT JOIN gender g2 ON v.gender_id = g2.gender_id
       LEFT JOIN age_range a2 ON v.age_range_id = a2.age_range_id

Is there an efficient way to show gender name on both employee and visitor without joining twice?

I have also tried sub-query in the Select section:

SELECT e.emp_id
      ,e.emp_name
      ,(SELECT g1.gender_name || ' ' || a1.age_range_name
          FROM gender g1, age_range a1
         WHERE e.gender_id = g1.gender_id AND e.age_range_id = a1.age_range_id)
           emp_gender_and_age_range
      ,g1.gender_name || ' ' || a1.age_range_name
           emp_gender_and_age_range
      ,v.visitor_name
      ,(SELECT g2.gender_name || ' ' || a2.age_range_name
          FROM gender g2, age_range a2
         WHERE v.gender_id = g2.gender_id AND v.age_range_id = a2.age_range_id)
           visitor_gender_and_age_range
  FROM employee e
       -- LEFT JOIN gender g1 ON e.gender_id = g1.gender_id
       -- LEFT JOIN age_range a1 ON e.age_range_id = a1.age_range_id
       LEFT JOIN visitor v ON e.emp_id = v.emp_id
       -- LEFT JOIN gender g2 ON v.gender_id = g2.gender_id
       -- LEFT JOIN age_range a2 ON v.age_range_id = a2.age_range_id

However, the problem with the above query is, visitor_gender_and_age_range returns null for Lily

emp_id | emp_name | emp_gender_and_age_range | visitor_name | visitor_gender_and_age_range
---------------------------------------------------------------------------------------------------
101    | adam     | male 36-45               | john         | male 36-45
101    | adam     | male 36-45               | lily         | **NULL**      
102    | ashley   | female 26-35             | jeff         | male 26-35       
103    | Cody     | male 46-55               | null         | null

Note: I haven't particularly tested the above query. However, my tables are exactly similar to the above example.

Thank you.

CodePudding user response：

No. In both cases (employee and visitor) the gender and age range are lookup codes. Each of those codes must be looked up in the gender and age_range tables separately.

Each lookup requires its own join because each one could return a different row.

CodePudding user response：

No, you can't, but you can use SQL scalar macros to make it shorter/easier to read in case of Oracle 21 .

Example with SQL Scalar macro: Full example on DBFiddle

create or replace function get_gender_age(p_gender_id int, p_age_range_id int)
  return varchar2 sql_macro(scalar)
is
begin
  return q'[(
    (SELECT gender_name from gender where gender_id = p_gender_id)
    ||' '||
    (SELECT age_range_name from age_range where age_range_id = p_age_range_id)
  )]';
end;
/

and query:

SELECT e.emp_id
      ,e.emp_name
      ,get_gender_age(e.gender_id,e.age_range_id) as emp_gender_and_age_range
      ,v.visitor_name
      ,get_gender_age(v.gender_id,v.age_range_id) as vis_gender_and_age_range
FROM employee e
     LEFT JOIN visitor v ON e.emp_id = v.emp_id

(You can also use inline PL/SQL functions, but I wouldn't suggest it because of lower performance in this case)

Or you can use CTE:

with gender_age as (
    SELECT 
         g.gender_id
        ,a.age_range_id
        ,g.gender_name || ' ' || a1.age_range_name as gender_and_age_range
    FROM gender g, age_range a
)
SELECT e.emp_id
      ,e.emp_name
      ,g1.gender_and_age_range as emp_gender_and_age_range
      ,v.visitor_name
      ,g2.gender_and_age_range as vis_gender_and_age_range
FROM 
    employee e
    LEFT JOIN gender_age g1 USING(gender_id, age_range_id)
    LEFT JOIN visitor v ON e.emp_id = v.emp_id
    LEFT JOIN gender_age g2 USING(gender_id, age_range_id)

But anyway, it's still 2 lookups.

CodePudding user response：

Is there an efficient way to show gender name on both employee and visitor without joining twice?

Joining twice is the standard way of joining these relations according to relational algebra.

Also you need to consider that joining twice can be quite efficient if the index gender (gender_id) is available. I'll take no time at all to resolve both joins.

I fail to see the concern you have about this.