survey_pin with join tables

survey_pin.sql

select userid -- from table prefix_user_info_data
		, p1.survey_pin
		,  (select count(sm.id) from survey sm where sm.survey_pin = p1.survey_pin) as cnt_rows_for_pin
		, null as ResponseID, null as Started, null as Ended, null as TotalTime, null as LastEdit, null as IP, null as 'Language', null as UniqueIdentifier, null as NetworkUser
		, ifnull(ssd.user_email, p1.email) as Email
		, ssd.user_name, ssd.user_dob -- , ssd.*
		-- main part of 5 partners
		, p1.id  as P1_id,  p1.partnerName as P1_Name, p1.partnerType as P1_Rel
			, p1.vaginal as P1_V_Sex, p1.vaginalSexNumber as P1_V_Sex_N, p1.vaginalSexCondomNumber as P1_V_Sex_Cond_N
			, p1.oral as P1_O_Sex, p1.oralSexNumber as P1_O_Sex_N, p1.oralSexCondomNumber as P1_O_Sex_Cond_N
			, p1.anal as P1_A_Sex, p1.analSexNumber as P1_A_Sex_N, p1.analSexCondomNumber as P1_A_Sex_Cond_N
			, p1.otherWomen as P1_Sex_OW, p1.otherMan as P1_Sex_OM
			, p1.shootingUp as P1_shootingUp, p1.hivTest as P1_hivTest, p1.unprotectedCheck as P1_unprotectedCheck
			, p1.perceivedPartnerRiskCheck as P1_perceivedPartnerRiskCheck, p1.higherRisk as P1_higherRisk, p1.highrisk as P1_highrisk

		, p2.id  as P2_id,  p2.partnerName as P2_Name, p2.partnerType as P2_Rel
			, p2.vaginal as P2_V_Sex, p2.vaginalSexNumber as P2_V_Sex_N, p2.vaginalSexCondomNumber as P2_V_Sex_Cond_N
			, p2.oral as P2_O_Sex, p2.oralSexNumber as P2_O_Sex_N, p2.oralSexCondomNumber as P2_O_Sex_Cond_N
			, p2.anal as P2_A_Sex, p2.analSexNumber as P2_A_Sex_N, p2.analSexCondomNumber as P2_A_Sex_Cond_N
			, p2.otherWomen as P2_Sex_OW, p2.otherMan as P2_Sex_OM
			, p2.shootingUp as P2_shootingUp, p2.hivTest as P2_hivTest, p2.unprotectedCheck as P2_unprotectedCheck
			, p2.perceivedPartnerRiskCheck as P2_perceivedPartnerRiskCheck, p2.higherRisk as P2_higherRisk, p2.highrisk as P2_highrisk

		, p3.id  as P3_id,  p3.partnerName as P3_Name, p3.partnerType as P3_Rel
			, p3.vaginal as P3_V_Sex, p3.vaginalSexNumber as P3_V_Sex_N, p3.vaginalSexCondomNumber as P3_V_Sex_Cond_N
			, p3.oral as P3_O_Sex, p3.oralSexNumber as P3_O_Sex_N, p3.oralSexCondomNumber as P3_O_Sex_Cond_N
			, p3.anal as P3_A_Sex, p3.analSexNumber as P3_A_Sex_N, p3.analSexCondomNumber as P3_A_Sex_Cond_N
			, p3.otherWomen as P3_Sex_OW, p3.otherMan as P3_Sex_OM	
			, p3.shootingUp as P3_shootingUp, p3.hivTest as P3_hivTest, p3.unprotectedCheck as P3_unprotectedCheck
			, p3.perceivedPartnerRiskCheck as P3_perceivedPartnerRiskCheck, p3.higherRisk as P3_higherRisk, p3.highrisk as P3_highrisk

		, p4.id  as P4_id,  p4.partnerName as P4_Name, p4.partnerType as P4_Rel
			, p4.vaginal as P4_V_Sex, p4.vaginalSexNumber as P4_V_Sex_N, p4.vaginalSexCondomNumber as P4_V_Sex_Cond_N
			, p4.oral as P4_O_Sex, p4.oralSexNumber as P4_O_Sex_N, p4.oralSexCondomNumber as P4_O_Sex_Cond_N
			, p4.anal as P4_A_Sex, p4.analSexNumber as P4_A_Sex_N, p4.analSexCondomNumber as P4_A_Sex_Cond_N
			, p4.otherWomen as P4_Sex_OW, p4.otherMan as P4_Sex_OM
			, p4.shootingUp as P4_shootingUp, p4.hivTest as P4_hivTest, p4.unprotectedCheck as P4_unprotectedCheck
			, p4.perceivedPartnerRiskCheck as P4_perceivedPartnerRiskCheck, p4.higherRisk as P4_higherRisk, p4.highrisk as P4_highrisk

		, p5.id  as P5_id,  p5.partnerName as P5_Name, p5.partnerType as P5_Rel
			, p5.vaginal as P5_V_Sex, p5.vaginalSexNumber as P5_V_Sex_N, p5.vaginalSexCondomNumber as P5_V_Sex_Cond_N
			, p5.oral as P5_O_Sex, p5.oralSexNumber as P5_O_Sex_N, p5.oralSexCondomNumber as P5_O_Sex_Cond_N
			, p5.anal as P5_A_Sex, p5.analSexNumber as P5_A_Sex_N, p5.analSexCondomNumber as P5_A_Sex_Cond_N
			, p5.otherWomen as P5_Sex_OW, p5.otherMan as P5_Sex_OM
			, p5.shootingUp as P5_shootingUp, p5.hivTest as P5_hivTest, p5.unprotectedCheck as P5_unprotectedCheck
			, p5.perceivedPartnerRiskCheck as P5_perceivedPartnerRiskCheck, p5.higherRisk as P5_higherRisk, p5.highrisk as P5_highrisk
		-- tail
		 , null as  Self_HIV_Test_E, null as Self_HIV_Test_3M, null as Self_HIV_Test_Res, null as Self_3M_ISTI, null as Male_P_1Yr, null as Freq_Sex_Last_Yr, null as Freq_Cond_Sex_Yr, null as P_Sex_OW_Yr, null as P_Sex_OM_Yr, null as P_Sex_ID_Yr, null as City, null as Age, null as Ethnic, null as Education, null as Sex_Orient, null as Cond_Prot_HIV, null as Part_Aw_3M, null as Part_Aw_Where, null as Cond_Last_Sex, null as Children, null as Child_Num, null as Pregnant, null as BC_Use, null as BC_Use_Abstinence, null as BC_Use_Condom, null as BC_Use_Pill, null as BC_Use_Tubes_tied  , null as BC_Use_Depo, null as BC_Use_Patch, null as BC_Use_IUD, null as BC_Use_Diaphragm, null as BC_Use_Withdrawal, null as WK_O_Home, null as WK_Hours, null as Pub_Assist, null as Age_Fs_Sex, null as Alc_Bef_Sex_3M, null as Drg_Bef_Sex_3M, null as Sex_Drugs_Weed, null as Sex_Drugs_Cocaine, null as Sex_Drugs_Ecstacy, null as Sex_Drugs_Poppers, null as Sex_Drugs_Speed, null as Sex_Drugs_Other, null as IDU_3M, null as Live_With	
	from survey p1
		left join survey p2 on p2.id = (select min(sq.id) from survey sq 
					where sq.survey_pin = p1.survey_pin and sq.id > p1.id )
		left join survey p3 on p3.id = (select min(sq.id) from survey sq 
					where sq.survey_pin = p1.survey_pin and sq.id > p2.id )
		left join survey p4 on p4.id = (select min(sq.id) from survey sq 
					where sq.survey_pin = p1.survey_pin and sq.id > p3.id )
		left join survey p5 on p5.id = (select min(sq.id) from survey sq 
					where sq.survey_pin = p1.survey_pin and sq.id > p4.id )
		-- from original sql-select for userid in table prefix_user_info_data
		left join prefix_user_info_data DATA ON p1.survey_pin = data.data and data.fieldid = 8
		-- try to get extra data from table screening_survey_detail with name of db !!!! w2winstruments
		left join w2winstruments.screening_survey_detail ssd on ssd.survey_pin = p1.survey_pin 

	where p1.survey_pin <> 0 -- exclude test data
		and p1.ID = (select min(sm.id) from survey sm where sm.survey_pin = p1.survey_pin)
	order by p1.survey_pin

test data result.csv

SELECT data.userid, survey.*
FROM survey
LEFT JOIN prefix_user_info_data DATA ON survey.survey_pin = data.data and data.fieldid = 8
WHERE survey.survey_pin <> 0
ORDER BY userid, id