SQL one to many join help
Posted on 2011-10-14
I need help with a join issue. It is for an informix database but I imagine this solution is standard SQL:
I am trying to create an export of data that has a table with one id that connects with a table that may have no rows, one row, or twenty rows that match a particular id. I just want to take say the first 5 that it finds. I need this to be consistent since I am creating a flat file from it and if there are no results it will just be an empty string.
I am hard-coding cucref1, cucref2, cucref3, etc. But the data I want is from a field called cucref from a table called ref_ref and a single id can have 1-5.
Is there a way to use a join to get the first instance for a certain id and select that, then get the 2nd instance and select that, and the 3rd instance and get that, etc. If there was no data then it would just come back blank.
Here is the current select query:
select i.id, a.common_app_id, i.firstname, i.lastname, i.middlename, a.pref_name, p.birth_date, 'IN' as International, p.hispanic, p.race, p.citz, i.ctry,
p.sex, a.email, i.phone, a.doc_ctgry, a.plan_enr_sess, a.plan_enr_yr, a.fa, a.early_decsn, a.discipline, a.first_gen, a.cl, a.cnslr_init, e.prim_sch, e.ceeb, e.grad_date,
'School City' as city, 'CA' as state, 91711 as zip, a.legacy, 'IMPTCT' as vip, 'JJV' as staff_init, 1 as score, 'EM' as cucref1, 'CV' as cucref2, 'S12' as cucref3,
'' as cucref4, '' as cucref5, a.hm_merit, a.exam_doc,
'C' as stat1, a.cl, '' as resrc2
from id_rec i, adm_rec a, profile_rec p, ed_rec e where i.id = p.id and i.id = a.id and i.id = e.id and a.plan_enr_yr = '2011' and e.prim_sch = 1