We help IT Professionals succeed at work.
Get Started

Surveys Readability Query: perform cross 6 table (joined or not) select, but constraining records to first table

intellisource
intellisource asked
on
2 Comments
1 Solution
417 Views
Last Modified: 2012-05-11
hi everybody,

i need to perform a selection similar to the following:

SELECT 
	C.text_ConsumerName AS `Consumer Name`, 
	SQ.smallint_SortOrder AS `Question Order`, 
	SQ.text_QuestionContent AS `Question Content`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.bigint_SupplierID, 
		SO.smallint_SortOrder
	) AS `Option Order`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.text_SupplierName, 
		IF (
			SO.tinyint_OptionType = 1 OR SO.tinyint_OptionType = 3, 
			SR.text_ConsumerText, 
			SO.text_OptionContent
		)
	) AS `Option Content` 
FROM 
	24_surveyresponse SR, 
	21_consumersurveys CS, 
	19_consumers C, 
	22_surveyquestions SQ, 
	23_surveyoptions SO, 
	5_suppliers S 
WHERE 
	SR.bigint_ConsumerSurveyID = CS.bigint_ConsumerSurveysID AND 
	CS.bigint_ConsumerID = C.bigint_ConsumerID AND 
	CS.bigint_SurveyType = SQ.bigint_TypeID AND 
	SQ.bigint_QuestionID = SO.bigint_QuestionID AND 
	SR.bigint_ResponseID = IF(
		SO.tinyint_OptionType > 3, 
		S.bigint_SupplierID, 
		SO.bigint_OptionID
	) AND 
	SR.bigint_ConsumerSurveyID = 1 
GROUP BY 
		`Consumer Name`, 
		`Question Order`, 
		`Question Content`, 
		`Option Order`, 
		`Option Content` 
ORDER BY 
	`Question Order` ASC,
	`Option Order` ASC;

Open in new window


which also gets the same result as a LEFT JOIN:

SELECT 
	C.text_ConsumerName AS `Consumer Name`, 
	SQ.smallint_SortOrder AS `Question Order`, 
	SQ.text_QuestionContent AS `Question Content`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.bigint_SupplierID, 
		SO.smallint_SortOrder
	) AS `Option Order`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.text_SupplierName, 
		IF (
			SO.tinyint_OptionType = 1 OR SO.tinyint_OptionType = 3, 
			SR.text_ConsumerText, 
			SO.text_OptionContent
		)
	) AS `Option Content` 
FROM 
	24_surveyresponse SR 
LEFT JOIN (
		21_consumersurveys CS, 
		19_consumers C, 
		22_surveyquestions SQ, 
		23_surveyoptions SO, 
		5_suppliers S 
	) ON (
		SR.bigint_ConsumerSurveyID = CS.bigint_ConsumerSurveysID AND 
		CS.bigint_ConsumerID = C.bigint_ConsumerID AND 
		CS.bigint_SurveyType = SQ.bigint_TypeID AND 
		SQ.bigint_QuestionID = SO.bigint_QuestionID AND 
		SR.bigint_ResponseID = IF(
			SO.tinyint_OptionType > 3, 
			S.bigint_SupplierID, 
			SO.bigint_OptionID
		)
	)
WHERE 
	SR.bigint_ConsumerSurveyID = 1 
GROUP BY 
		`Consumer Name`, 
		`Question Order`, 
		`Question Content`, 
		`Option Order`, 
		`Option Content` 
ORDER BY 
	`Question Order` ASC,
	`Option Order` ASC;

Open in new window


but where the previous query returns the 22 records in sheet Readable Query of the attached XLSX document.

however there are only 12 in the saved survey response rows (24_surveyresponse in the XLSX) from the dynamic database driven web form (screenshot is attached, and the form can be viewed here, firefox 4 recommended).

how would i be able to do a selection similar to the above, which only returns 12 records?
the other associated tables mentioned below are also sheets in the XLSX:

21_consumersurveys
19_consumers
22_surveyquestions
23_surveyoptions
5_suppliers

Some of the tables have been truncated to display only the required data selected, there are 3,279 rows if i do not constrain the data by grouping the temporary fields.

24_surveyresponse.bigint_ResponseID contains either an option id (where 24_surveyresponse.text_ConsumerText is blank there is no text input field bound to the radio/checkbox button), or a supplier id (for radio/checkbox supplier listings, 24_surveyresponse.text_ConsumerText is blank too because the supplier id is saved in 24_surveyresponse.bigint_ResponseID).

the tables are in an excel workbook for easier viewing; formatting and aligning text on any forum is the pits!
basically, how can i select only 12 records from the available data.

constraining suppliers by another WHERE option returned 12 records but most of the fields are empty (Broken Query in the XLSX)

SELECT 
	C.text_ConsumerName AS `Consumer Name`, 
	SQ.smallint_SortOrder AS `Question Order`, 
	SQ.text_QuestionContent AS `Question Content`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.bigint_SupplierID, 
		IF (
			SO.tinyint_OptionType = 1 OR SO.tinyint_OptionType = 3, 
			SO.smallint_SortOrder, 
			SR.bigint_ResponseID
		)
	) AS `Option Order`, 
	IF(
		SO.tinyint_OptionType > 3, 
		S.text_SupplierName, 
		IF (
			SO.tinyint_OptionType = 1 OR SO.tinyint_OptionType = 3, 
			SR.text_ConsumerText, 
			SO.text_OptionContent
		)
	) AS `Option Content` 
FROM 
	24_surveyresponse SR
LEFT JOIN (
		21_consumersurveys CS, 
		19_consumers C, 
		22_surveyquestions SQ, 
		23_surveyoptions SO, 
		27_leadssent LS, 
		13_prospectleadsent PS, 
		5_suppliers S
	) ON (
		SR.bigint_ConsumerSurveyID = CS.bigint_ConsumerSurveysID AND 
		CS.bigint_ConsumerID = C.bigint_ConsumerID AND 
		CS.bigint_SurveyType = SQ.bigint_TypeID AND 
		CS.bigint_LeadID = LS.bigint_LeadID AND 
		CS.bigint_ReferenceID = PS.bigint_ProspectID AND 
		SQ.bigint_QuestionID = SO.bigint_QuestionID AND 
		LS.bigint_SupplierID = S.bigint_SupplierID AND 
		PS.bigint_SupplierID = S.bigint_SupplierID AND 
		IF(
			SO.tinyint_OptionType > 3, 
			(
				SR.bigint_ResponseID = LS.bigint_SupplierID OR 
				SR.bigint_ResponseID = PS.bigint_SupplierID
			), 
			SR.bigint_ResponseID = SO.bigint_OptionID
		)
	) 
WHERE 
	SR.bigint_ConsumerSurveyID = 1 
GROUP BY 
		`Consumer Name`, 
		`Question Order`, 
		`Question Content`, 
		`Option Order`, 
		`Option Content` 
ORDER BY 
	`Question Order` ASC,
	`Option Order` ASC;

Open in new window


not sure if there is an easier way to go about it, but i seem to have gotten lost between the start of the query and the finish. please help!

Sincerely,

Pierre "Greywacke" du Toit

PS:

then again - if this issue somehow cannot be resolved with the current form, in the database selection, then perhaps i should simplify the query: save either the option / checkbox texts selected, or supplier names in the blank 24_surveyresponse.text_ConsumerText entries.

the form is created with the following php function:

function getquestions($surveytype,$consumerid,$leadid=0,$refid=0) {
	global $conn;
	$sql = "SELECT * 
			FROM 
				22_surveyquestions 
			LEFT JOIN 
				23_surveyoptions 
			ON (
				22_surveyquestions.bigint_QuestionID = 23_surveyoptions.bigint_QuestionID
			) 
			WHERE 
				22_surveyquestions.bigint_TypeID = ".$surveytype." 
			ORDER BY 
				22_surveyquestions.smallint_SortOrder ASC, 
				23_surveyoptions.smallint_SortOrder ASC;";
	$result = mysql_query_errors($sql, $conn , __FILE__ , __LINE__ );
	$html = "<form id=\"form_survey\" name=\"form_survey\" method=\"post\" accept-charset=\"UTF-8\" action=\"survey_handler.php\" onsubmit=\"return valform(this);\">".
			"<input name=\"consumersurveyid\" id=\"consumersurveyid\" type=\"hidden\" value=\"".$GLOBALS["q"]."\" />".
			"<input name=\"includepath\" id=\"includepath\" type=\"hidden\" value=\"".$GLOBALS["p"]."\" />\n";
	$q = array();
	$q[0] = "";
	$q[1] = "";
	$shtml = "";
	$i = 0;
	if ($result) {
		while ($row = mysql_fetch_array($result)) {
			$q[1] = $row["text_QuestionContent"];
			if ($q[0] != $q[1]) {
				$i++;
				$n = 0;
				if ($q[0] != "") {
					$html .= "	</p>\n";
				}
				$q[0] = $q[1];
				$html .= "	<p><label for=\"radio_answer_".$i."_1\"><strong>".$i."). ".$q[1]."</strong></label></p>\n".
						 "	<p>\n";
				$shtml = "";
			}
			$n++;
			$a = xmlentities($row["text_OptionContent"]);
			$sid = 0;
			switch ($row["tinyint_OptionType"]) {
				case 1:		// radio select + text input
					$html .= "	&nbsp; <input type=\"radio\" name=\"group_".$i."\" id=\"radio_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" onfocus=\"document.form_survey.text_".$row["bigint_OptionID"].".focus();\" />".
							 "<label for=\"radio_".$i."_".$n."\" >&nbsp; ".$a."<br /> &nbsp; ".
							 "<input type=\"text\" name=\"text_".$row["bigint_OptionID"]."\" id=\"text_".$row["bigint_OptionID"]."\" value=\"\" /></label><br />\n";
					break;
				case 2:		// checkbox
					$html .= "	&nbsp; <input type=\"checkbox\" name=\"group_".$i."[]\" id=\"check_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" />".
							 "<label for=\"check_".$i."_".$n."\" >&nbsp; ".$a."</label><br />\n";
					break;
				case 3:		// checkbox + text input
					$html .= "	&nbsp; <input type=\"checkbox\" name=\"group_".$i."[]\" id=\"check_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" onfocus=\"document.form_survey.text_".$row["bigint_OptionID"].".focus();\" />".
							 "<label for=\"check_".$i."_".$n."\" >&nbsp; ".$a."<br /> &nbsp; ".
							 "<input type=\"text\" name=\"text_".$row["bigint_OptionID"]."\" id=\"text_".$row["bigint_OptionID"]."\" value=\"\" /></label><br />\n";
					break;
				case 4:		// supplier names listing radio
					$sql0 = "SELECT 
								5_suppliers.bigint_SupplierID, 
								5_suppliers.text_SupplierName 
							FROM 
								5_suppliers 
							LEFT JOIN (
								27_leadssent, 
								13_prospectleadsent
							) ON (
								5_suppliers.bigint_SupplierID = 27_leadssent.bigint_SupplierID AND 
								5_suppliers.bigint_SupplierID = 13_prospectleadsent.bigint_SupplierID
							) 
							WHERE 
								27_leadssent.bigint_LeadID = ".$leadid." OR 
								13_prospectleadsent.bigint_ProspectID = ".$refid." 
							GROUP BY 
								5_suppliers.text_SupplierName;";
					$result0 = mysql_query_errors($sql0, $conn , __FILE__ , __LINE__ );
					if ($result0) {
						while ($row0 = mysql_fetch_array($result0)) {
							$a = $row0["text_SupplierName"];
							$html .= "	&nbsp; <input type=\"radio\" name=\"group_".$i."\" id=\"radio_".$i."_".$n."\" value=\"".$row0["bigint_SupplierID"]."\" />".
									 "<label for=\"radio_".$i."_".$n."\" >&nbsp; ".$a."</label><br />\n";
							if ($n<mysql_num_rows($result0)) $n++;
						}
					}
					break;
				case 5:		// supplier names listing checkbox
					$sql0 = "SELECT 
								5_suppliers.bigint_SupplierID, 
								5_suppliers.text_SupplierName 
							FROM 
								5_suppliers 
							LEFT JOIN (
								27_leadssent, 
								13_prospectleadsent
							) ON (
								5_suppliers.bigint_SupplierID = 27_leadssent.bigint_SupplierID AND 
								5_suppliers.bigint_SupplierID = 13_prospectleadsent.bigint_SupplierID
							) 
							WHERE 
								27_leadssent.bigint_LeadID = ".$leadid." OR 
								13_prospectleadsent.bigint_ProspectID = ".$refid." 
							GROUP BY 
								5_suppliers.text_SupplierName;";
					$result0 = mysql_query_errors($sql0, $conn , __FILE__ , __LINE__ );
					if ($result0) {
						while ($row0 = mysql_fetch_array($result0)) {
							$a = $row0["text_SupplierName"];
							$html .= "	&nbsp; <input type=\"checkbox\" name=\"group_".$i."[]\" id=\"radio_".$i."_".$n."\" value=\"".$row0["bigint_SupplierID"]."\" />".
									 "<label for=\"radio_".$i."_".$n."\" >&nbsp; ".$a."</label><br />\n";
							if ($n<mysql_num_rows($result0)) $n++;
						}
					}
					break;
				default:	// radio select
					$html .= "	&nbsp; <input type=\"radio\" name=\"group_".$i."\" id=\"radio_".$i."_".$n."\" value=\"".$row["bigint_OptionID"]."\" />".
							 "<label for=\"radio_".$i."_".$n."\" >&nbsp; ".$a."</label><br />\n";
			}
		}
		mysql_free_result($result);
	}
	$html .= "	</p>\n";
	$html .= "	<p><input type=\"submit\" value=\"Submit!\" /><input type=\"reset\" value=\"Reset...\" /></p>\n";
	$html .= "</form>";
	return array($i, $html);
}

Open in new window


the data could then be sent via hidden fields. or picked up from the database in the handler somehow... but why send a bunch of text back to the database, when it's already in it? 0o
Test-Survey-One.jpg
Survey-Readability-Queries---Tab.xlsx
Comment
Watch Question
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE