Date: 05/14/05 (Code WTF)    Keywords: no keywords

select p.person_id_i, coalesce((select s.new_anketa_state from stack s
where s.person_id_i=p.person_id_i),-1) as "in_stack", CASE WHEN
to_char(p.per_regdate,'YYYY')=to_char(date 'now','YYYY') THEN
to_char(p.per_regdate,'DD Mon') ELSE to_char(p.per_regdate,'DD Mon
YYYY')END as per_regdate, p.per_anketa_state, p.per_screenname,
p.per_email_m, p.per_sex, p.per_seeking, cit.city_name_ru,
reg.region_name_ru, co.co_name_ru, p.per_zip_i, r.rel_name_ru,
lan.lan_name_ru, tm.tm_name_ru, to_char(datetime(p.per_birthdate), 'dd
month yyyy') as "per_birthdate", occu.occu_name_ru, ind.ind_name_ru,
inc.income_name_ru, relig.relig_name_ru, hei.height_name_ru,
wei.weight_name_ru, hc.hc_name_ru, ec.ec_name_ru, bt.bt_name_ru,
act.act_name_ru, sm.smoke_name_ru, dr.drink_name_ru,
ms.m_stat_name_ru, ha.have_ch_name_ru, cu.cus_name_ru, case when
mp.want_ch_id_i=1 then 'Yes' when mp.want_ch_id_i=0 then 'No' else
'not filled yet' end as "want_ch_name", case when p.per_relocate=1
then 'Yes' when p.per_relocate=0 then 'No' else 'not filled yet' end
as "per_relocate_name", et.etn_name_ru, ed.edu_name_ru,
po.pol_sen_name_ru, ap.ap_s_name_ru, ins.is_name_ru,
bm.bit_per_traits, bm.bit_per_act, bm.bit_fav_cuisines,
bm.bit_like_music, bm.bit_like_read, bm.bit_languages, bm.bit_go_to,
bm.bit_phys_act, bm.cat , bm.dog , bm.fish , bm.bird , bm.rodent ,
bm.exotic , itm.txt1, case when itm.txt1_state=1 then 'approoved' when
itm.txt1_state = 3 then 'banned' when itm.txt1_state = 0 then
'waiting' when itm.txt1 is null then 'not filled yet' else 'unchecked'
end as "txt1_stat", itm.txt2, case when itm.txt2_state=1 then
'approoved' when itm.txt2_state = 3 then 'banned' when itm.txt2_state
= 0 then 'waiting' when itm.txt2 is null then 'not filled yet' else
'unchecked' end as "txt2_stat", itm.txt3, case when itm.txt3_state=1
then 'approoved' when itm.txt3_state = 3 then 'banned' when
itm.txt3_state = 0 then 'waiting' when itm.txt3 is null then 'not
filled yet' else 'unchecked' end as "txt3_stat", itm.txt4, case when
itm.txt4_state=1 then 'approoved' when itm.txt4_state = 3 then
'banned' when itm.txt4_state = 0 then 'waiting' when itm.txt4 is null
then 'not filled yet' else 'unchecked' end as "txt4_stat", itm.txt5,
case when itm.txt5_state=1 then 'approoved' when itm.txt5_state = 3
then 'banned' when itm.txt5_state = 0 then 'waiting' when itm.txt5 is
null then 'not filled yet' else 'unchecked' end as "txt5_stat",
itm.foto1, itm.foto1_state, itm.foto1_width, itm.foto1_height,
itm.foto2, itm.foto2_state, itm.foto2_width, itm.foto2_height,
itm.foto3, itm.foto3_state, itm.foto3_width, itm.foto3_height,
itm.foto4, itm.foto4_state, itm.foto4_width, itm.foto4_height,
itm.sound, itm.sound_file from person p LEFT JOIN country co ON
(co.co_id_i=p.co_id_i) LEFT JOIN region reg ON
(reg.region_id_i=p.region_id_i) LEFT JOIN city cit ON
(cit.city_id_i=p.city_id_i) LEFT JOIN time_zones tm ON
(tm.tm_id_i=p.tm_id_i) LEFT JOIN bit_mask bm ON
(bm.person_id_i=p.person_id_i) LEFT JOIN spoken_languages lan ON
(lan.lan_id_i=p.lan_id_i) LEFT JOIN m_stat ms ON
(ms.m_stat_id_i=p.m_stat_id_i) LEFT JOIN relationship r ON
(r.rel_id_i=p.rel_id_i), my_profile mp LEFT JOIN education ed ON
(ed.edu_id_i=mp.edu_id_i) LEFT JOIN etnicity et ON
(et.etn_id_i=mp.etn_id_i) LEFT JOIN custody cu ON
(cu.cus_id_i=mp.cus_id_i) LEFT JOIN have_children ha ON
(ha.have_ch_id_i=mp.have_ch_id_i) LEFT JOIN drink dr ON
(dr.drink_id_i=mp.drink_id_i) LEFT JOIN smoke sm ON
(sm.smoke_id_i=mp.smoke_id_i) LEFT JOIN occupation occu ON
(occu.occu_id_i=mp.occu_id_i) LEFT JOIN industry ind ON
(ind.ind_id_i=mp.ind_id_i) LEFT JOIN income inc ON
(inc.income_id_i=mp.income_id_i) LEFT JOIN height hei ON
(hei.height_id_i=mp.height_id_i) LEFT JOIN weight wei ON
(wei.weight_id_i=mp.weight_id_i) LEFT JOIN hair_color hc ON
(hc.hc_id_i=mp.hc_id_i) LEFT JOIN eye_color ec ON
(ec.ec_id_i=mp.ec_id_i) LEFT JOIN body_type bt ON
(bt.bt_id_i=mp.bt_id_i) LEFT JOIN activity act ON
(act.act_id_i=mp.act_id_i) LEFT JOIN religion relig ON
(relig.relig_id_i=mp.relig_id_i), my_personality me LEFT JOIN
politic_sense po ON (po.pol_sen_id_i=me.pol_sen_id_i) LEFT JOIN
apearance_sense ap ON (ap.ap_s_id_i=me.ap_s_id_i) LEFT JOIN
intelegence_sense ins ON (ins.is_id_i=me.is_id_i), items itm where
itm.person_id_i = '1073742786' and mp.person_id_i='1073742786' and
p.person_id_i='1073742786' and me.person_id_i='1073742786'

Source: http://www.livejournal.com/community/code_wtf/5371.html