I have two tables which are related in the following way:

Table A is a list of college courses and instances of that course, for example the "course" Maths runs on a Tuesday and a Thursday and therefore has two "course_periods". This is described in the following table:

Table A

There is another table which connects all the "course periods" to their parent "courses" and looks like this:

Table B

I would like to produce the last below table which joins the two tables A & B in the following way:

I simply can't find the correct join statement to produce the desired result.

CodePudding user response：

You must join TableB to 2 copies of TableA:

SELECT b.destination destination_id,
       a2.name name_course_period,
       b.source source_id,
       a1.name name_course
FROM TableB b 
INNER JOIN TableA a1 ON a1.id = b.source
INNER JOIN TableA a2 ON a2.id = b.destination;

See the demo.

CodePudding user response：

Recreating your tables as cte, you need to start with table a as your base table. Join this table to b.

You then need to join back to a to get the name of the destination.

with a as (
select '0001' id, 'Maths' name, 'course' type
union all select '0002', 'Maths (Thursday)','course_period' 
union all select '0003', 'Maths (Tuesday)', 'course_period'
)
, b as (
select '0001' id, '0001' source, '0002' destination
union all select '0002', '0001', '0003'
)
select 
b.id as destination_id
, ref.name as name_course_period
, b.source as source_id
, a.name as name_course
from a 
inner join b on b.source = a.id 
inner join a ref on ref.id = b.destination  --join back to a for name