Skip to content

Instantly share code, notes, and snippets.

@skyzha

skyzha/Query select on select from join 3 table

Created August 17, 2019 02:26
Show Gist options
  • Save skyzha/499cf91ea842f75ed72c0c1fb6551675 to your computer and use it in GitHub Desktop.
Save skyzha/499cf91ea842f75ed72c0c1fb6551675 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Query select on select from join 3 table
select * from (
select selectone.id, selectone.role, selectone.myb_personnelNo, selectone.myb_fullname from (
select
detail.id as detail_id, detail.training_event_id, detail.participant_id, detail.mentor_id,
ha.id as pivot_id , ha.assessor_id,
pa.id as id, 'participant' as Role, pa.myb_personnelNo, pa.myb_fullname
from training_details as detail
inner join training_details_has_assessors as ha on detail.id = ha.training_detail_id
inner join participants as pa on detail.participant_id = pa.id
union
select
detail.id as detail_id, detail.training_event_id, detail.participant_id, detail.mentor_id,
ha.id as pivot_id, ha.assessor_id,
me.id as id, 'mentor' as Role, me.myb_personnelNo, me.myb_fullname
from training_details as detail
inner join training_details_has_assessors as ha on detail.id = ha.training_detail_id
inner join mentors as me on detail.mentor_id = me.id
union
select
detail.id as detail_id, detail.training_event_id, detail.participant_id, detail.mentor_id,
ha.id as pivot_id, ha.assessor_id,
ar.id as id, 'assessor' as Role, ar.myb_personnelNo, ar.myb_fullname
from training_details as detail
inner join training_details_has_assessors as ha on detail.id = ha.training_detail_id
inner join assessors as ar on ha.assessor_id = ar.id
) as selectone
where selectone.training_event_id = 5
) as selecttwo
where selecttwo.myb_fullname like '%anna%'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment