Skip to main content
added 752 characters in body
Source Link
ypercubeᵀᴹ
  • 98.6k
  • 13
  • 215
  • 305

Not simple:

SELECT 
    b.pers,
    t.description
FROM
    BaseTable AS b
  CROSS JOIN
    ( SELECT DISTINCT test 
      FROM TestTable
      WHERE grp = 'G1'
    ) AS d
  LEFT JOIN 
    TestTable AS ti
      ON  ti.grp = 'G1'
      AND ti.test = d.test
      AND ti.type = b.type
  LEFT JOIN 
    TestTable AS t
      ON  t.grp = 'G1'
      AND t.test = d.test
      AND t.type <=> ti.type 
ORDER BY 
    b.pers,
    d.test ;

Tested at SQLfiddleSQLfiddle


Another way, similar to the above in logic but with different join syntax:

SELECT 
    b.pers,
    t.description
FROM
    BaseTable AS b
  CROSS JOIN
    ( SELECT DISTINCT test 
      FROM TestTable
      WHERE grp = 'G1'
    ) AS d
  LEFT JOIN 
    TestTable AS t
      ON  t.grp = 'G1'
      AND t.test = d.test
      AND t.type <=> 
          ( SELECT ti.type
            FROM TestTable AS ti
            WHERE ti.grp = 'G1'
              AND ti.test = d.test
              AND ti.type = b.type
       --   ORDER BY (ti.type IS NULL)
              LIMIT 1
          ) 
ORDER BY 
    b.pers,
    d.test ;

Not simple:

SELECT 
    b.pers,
    t.description
FROM
    BaseTable AS b
  CROSS JOIN
    ( SELECT DISTINCT test 
      FROM TestTable
      WHERE grp = 'G1'
    ) AS d
  LEFT JOIN 
    TestTable AS ti
      ON  ti.grp = 'G1'
      AND ti.test = d.test
      AND ti.type = b.type
  LEFT JOIN 
    TestTable AS t
      ON  t.grp = 'G1'
      AND t.test = d.test
      AND t.type <=> ti.type 
ORDER BY 
    b.pers,
    d.test ;

Tested at SQLfiddle

Not simple:

SELECT 
    b.pers,
    t.description
FROM
    BaseTable AS b
  CROSS JOIN
    ( SELECT DISTINCT test 
      FROM TestTable
      WHERE grp = 'G1'
    ) AS d
  LEFT JOIN 
    TestTable AS ti
      ON  ti.grp = 'G1'
      AND ti.test = d.test
      AND ti.type = b.type
  LEFT JOIN 
    TestTable AS t
      ON  t.grp = 'G1'
      AND t.test = d.test
      AND t.type <=> ti.type 
ORDER BY 
    b.pers,
    d.test ;

Tested at SQLfiddle


Another way, similar to the above in logic but with different join syntax:

SELECT 
    b.pers,
    t.description
FROM
    BaseTable AS b
  CROSS JOIN
    ( SELECT DISTINCT test 
      FROM TestTable
      WHERE grp = 'G1'
    ) AS d
  LEFT JOIN 
    TestTable AS t
      ON  t.grp = 'G1'
      AND t.test = d.test
      AND t.type <=> 
          ( SELECT ti.type
            FROM TestTable AS ti
            WHERE ti.grp = 'G1'
              AND ti.test = d.test
              AND ti.type = b.type
       --   ORDER BY (ti.type IS NULL)
              LIMIT 1
          ) 
ORDER BY 
    b.pers,
    d.test ;
Source Link
ypercubeᵀᴹ
  • 98.6k
  • 13
  • 215
  • 305

Not simple:

SELECT 
    b.pers,
    t.description
FROM
    BaseTable AS b
  CROSS JOIN
    ( SELECT DISTINCT test 
      FROM TestTable
      WHERE grp = 'G1'
    ) AS d
  LEFT JOIN 
    TestTable AS ti
      ON  ti.grp = 'G1'
      AND ti.test = d.test
      AND ti.type = b.type
  LEFT JOIN 
    TestTable AS t
      ON  t.grp = 'G1'
      AND t.test = d.test
      AND t.type <=> ti.type 
ORDER BY 
    b.pers,
    d.test ;

Tested at SQLfiddle