Avatar of intellisource
intellisourceFlag for South Africa

asked on 

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

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
DatabasesMySQL ServerSQL

Avatar of undefined
Last Comment
intellisource
ASKER CERTIFIED SOLUTION
Avatar of intellisource
intellisource
Flag of South Africa image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of intellisource
intellisource
Flag of South Africa image

ASKER

ok seeing as i did not receive a solution, i looked into it further
Databases
Databases

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

62K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo