nozzy

with 
t1 as
(
select .... from table1 where .....
),
t2 as
(
select .... from table2 where .....
)

select ...... 
from t1
join t2 ....

select *
from
(
	select 
		p.email,
		cr.character_name,
		cs.class_name
	from players p
	inner join characters cr on cr.player_id = p.id
	inner join classes cs on cs.id = cr.class_id
)
pivot
(      
 MAX(character_name)
	for class_name in ('Priest' as 	"Priest", 
								'Mage' as 	"Mage", 
								'Warrior' as "Warrior", 
								'Rogue' as "Rogue")
)

select 
    p.email,
 decode(class_name, 'Priest', character_name, null)  as "Priest",
 decode(class_name, 'Mage', character_name, null)  as "Mage",
 decode(class_name, 'Warrior', character_name, null)  as "Warrior",
 decode(class_name, 'Rogue', character_name, null)  as "Rogue"
  from players p
  inner join characters cr on cr.player_id = p.id
  inner join classes cs on cs.id = cr.class_id