Solved

Not showing duplicate results using a for while loop

Posted on 2009-05-07
11
343 Views
Last Modified: 2013-12-12
I am trying not to show duplicated results, in this case vehicle_trim, and am trying to using the for while loop to do so.

Can this be done?

I'm new at this and can't seem to get this to work the way I want it to.

Thanks for your help!
This is the SQL :

$query_CRV = "SELECT * 

FROM ebizcardata

INNER JOIN Vehicle_Additional_Information 

ON ebizcardata.Stock_No = Vehicle_Additional_Information.Stock_No

WHERE Modell = 'Accord'

AND New_Used = 'New'

AND Ext_Color

IN ('White Diamond Pearl', 'Taffeta White')

ORDER BY Year ASC , STR_TO_DATE( Date_In_Stock, '%m/%d/%Y' ) ASC ";
 

This is the loop code (some of it)

<?php } while ($row_CRV = mysql_fetch_assoc($CRV)); }?>
 

I don't want to duplicate = $row_CRV['vehicle_trim'];
 
 

I hope you don't need this but more of the ball of wax:
 

<!-- Start Accord Inventory -->

              <p class="copy" style="text-align:center"><?php if ($pageNum_CRV > 0) { // Show if not first page ?>

                <a href="<?php printf("%s?pageNum_CRV=%d%s", $currentPage, max(0, $pageNum_CRV - 1), $queryString_CRV.($Trim?'&Trim='.$Trim:'').($Body_Style?'&Body_Style='.$Body_Style:'').($Ex_Color?'&Ex_Color='.$Ex_Color:'')); ?>" class="bro"><< Previous Page</a>

                <?php } // Show if not first page ?>

             Showing <?php echo ($startRow_CRV + 1) ?> - <?php echo min($startRow_CRV + $maxRows_CRV, $totalRows_CRV) ?> of <?php echo $totalRows_CRV ?>

					New Honda <?php if ($Trim == "")

					{

										

					}

					else if ($Trim == "All")

					{

					

					}

					else 

					{

						echo $Trim;

					}

				?>

				Accords

				

              <?php if ($pageNum_CRV < $totalPages_CRV) { // Show if not last page ?>

                <a href="<?php printf("%s?pageNum_CRV=%d%s",  $currentPage, min($totalPages_CRV, $pageNum_CRV + 1), $queryString_CRV.($Trim?'&Trim='.$Trim:'').($Body_Style?'&Body_Style='.$Body_Style:'')); ?>"  class="bro">Next Page >></a>

                <?php } // Show if not last page ?></p>

				<?php  

				if ($row_CRV['Modell'] == "") 

				{

					print "<table cellpadding=\"0\" cellspacing=\"0\" bgcolor=\"#FFFFFF\" style=\"width:495px; padding-top:10px\">

                  <tr>

                      <td valign=\"top\" style=\"padding-bottom:3px; padding-left:10px; padding-right:10px\" align=\"Center\">";

					  

									extract($_POST);

									$lang_notice = "<font style=\"color:#006699; font-size:14px;\"><b>Sorry we have no Honda Accord $Trim $Body_Style s in our online inventory.</b></font><br><p class=\"copy\" style=\"padding-top:5px;\"><font color=\"#000000\">Please complete the form below as we do get new vehicles on a regular bases. 

							</font></p>";

									// Error messages

									$lang_error = "<span class=\"required\">Please complete the following fields:</span>";

									

									if (strlen($first_name)<2)

										{

										$error = "1";

										$info_error .= "<span class=\"copy\">Please enter your first name. </span><br>"; 

										$error_fn = $error_fn;

										}

									if (strlen($comments)<3)  

										{

										$error = "1";

										$info_error .= "<span class=\"copy\">You must type a message.</span><br>";  

										}

									if (strlen($area)<3 or strlen($prefix)<3 or strlen($sufix)<4) 

										{

										$error = "1";

										$info_error .= "<span class=\"copy\">Please enter your complete phone number.</span><br>";  

										}

									if (is_numeric($area) and is_numeric($prefix) and is_numeric($sufix)) 

															{

															

															}

														else

															{

															$error = "1";

															$info_error .= "<span class=\"copy\">Your phone number must be numeric.</span><br>";  

															}

									if (is_numeric($area) and is_numeric($prefix) and is_numeric($sufix)) 

															{

															

															}

														else

															{

															$error = "1";

															$info_error .= "<span class=\"copy\">Your phone number must be numeric.</span><br>";  

															}

									if ($error == "1") 

										{

										$info_notice = "<span style=\"color: " . $error_colour . "; font-weight: bold;\">" . $lang_error . "</span><br>"; 

										

										if (empty ($submit)) 

											{

											$info_error = "";

											$info_notice = $lang_notice;

											}	

									

									print "<form name=\"contact\" method=\"post\" style=\"margin: 0;\" action=\"colorado-accord.php?Trim=$Trim&Body_Style=$Body_Style\">

									<table  border=\"0\" cellspacing=\"2\" cellpadding=\"2\" bgcolor=\"#f1f1f1\" align=\"Center\">    

										<tr align=\"left\" valign=\"top\">

										  <td colspan=\"2\">$info_notice$info_error</td>

										</tr>

											<tr valign=\"top\">

											  <td align=\"left\" class=\"question_form\">

												<label for=\"first_name\"><font style=\"color:#006699; font-size:14px;\">First Name<span class=\"required\">*</span></font></label><br>

												<input tabindex=\"1\" name=\"first_name\" id=\"first_name\" type=\"text\" size=\"20\" maxlength=\"50\" value=\"$first_name\" >

											  </td>

											  <td align=\"left\" class=\"question_form\"> 

												<label for=\"last_name\"><font style=\"color:#006699; font-size:14px;\">Last Name</font></label><br>

												<input tabindex=\"2\" name=\"last_name\" id=\"last_name\" type=\"text\" size=\"20\" maxlength=\"50\" value=\"$last_name\" >

											  </td>

											</tr>

											<tr valign=\"top\">

											  <td align=\"left\" class=\"question_form\">

												<label for=\"email\"><font style=\"color:#006699; font-size:14px;\">Email Address</font></label><br> 			

													<input tabindex=\"3\" name=\"email\" id=\"email\" type=\"text\" size=\"20\" maxlength=\"80\" value=\"$email\" >

											  </td>

											  <td align=\"left\" class=\"question_form\"> 

												<label for=\"phone\"><font style=\"color:#006699; font-size:14px;\">Phone Number<span class=\"required\">*</span></font</label><br>												

													(<input tabindex=\"4\" type=\"text\" name=\"area\" id=\"phone\" size=\"2\" onKeyup=\"autotab(this, document.contact.prefix)\" maxlength=\"3\" value=\"$area\" >)

                                                                     <input tabindex=\"5\" type=\"text\" name=\"prefix\" id=\"phone\"  size=\"2\" onKeyup=\"autotab(this, document.contact.sufix)\" maxlength=\"3\" value=\"$prefix\" > 

                                                                     <input tabindex=\"6\" type=\"text\" name=\"sufix\" id=\"phone\"  size=\"3\" onKeyup=\"autotab(this, document.contact.how_contact)\" maxlength=\"4\" value=\"$sufix\" > 

											  </td>

											</tr>

											<tr valign=\"top\" class=\"question_form\">

											<td colspan=\"2\">

													<label for=\"how_contact\"><font style=\"color:#006699; font-size:14px;\">Perfered Contact </font></label><br>												

												  <select tabindex=\"7\" name=\"how_contact\" id=\"how_contact\" value=\"$how_contact\">

													<option value=\"Any\" >Any</option>

													<option value=\"Phone\" >Phone</option>

													<option value=\"Email\" >Email</option>

												  </select> 

											</td>

											</tr>

											<tr valign=\"top\" class=\"question_form\">

												<td align=\"left\" colspan=\"2\">

													<label for=\"comments\"><font style=\"color:#006699; font-size:14px;\">Message<span class=\"required\">*</span></font></label><br>						

													<textarea name=\"comments\" tabindex=\"8\" cols=\"50\" rows=\"5\">I am interested in a new Honda Accord "; echo $Trim; print " "; echo $Body_Style; print "that is not in your online inventory. Please contact me with more information.</textarea>

										</td>

										</tr>

										<tr valign=\"top\" class=\"question_form\">

											

										 <td align=\"center\" colspan=\"2\">

										   <input type=\"hidden\" name=\"date\" value=\"$date\">

					  						<input type=\"hidden\" name=\"IPaddr\" value=\"$IPaddr\"> 

											<input type=\"hidden\" name=\"Trim\" value=\"$Trim\">

					  						<input type=\"hidden\" name=\"Body_Style\" value=\"$Body_Style\"> 

											<input type=\"hidden\" name=\"submit\" value=\"1\"> 

										  <br>

										  <input name=\"submit\" tabindex=\"9\" type=\"image\" src=\"../images/submit.jpg\" onMouseOver=\"this.src='../images/submit_d.jpg';\" onMouseOut=\"this.src='../images/submit.jpg';\" align=\"absmiddle\" border=\"0\" id=\"submit\" value=\"$lang_submit\">

										 </td>

										</tr>

									  </table>

									</form>";

										}

									else

										{

										$user="";

										$pass="";

										$db="bkhondasales";

										$IPaddr=$_POST['IPaddr'];

										$date=$_POST['date'];

										$first_name=$_POST['first_name'];

										$last_name =$_POST['last_name'];

										$comments =$_POST['comments'];

										$area =$_POST['area'];

										$prefix =$_POST['prefix'];

										$sufix =$_POST['sufix'];

										$email=$_POST['email'];

										$how_contact=$_POST['how_contact'];

										mysql_connect('',$user,$pass);

										@mysql_select_db($db) or die( "Unable to select database, Please contact your administrator");

										$query = "INSERT INTO contact_table VALUES ('Accord Vehicle Search Page','$IPaddr','$date','$first_name','$last_name','$comments','$area','$prefix','$sufix','','$email','','','','','','','')";

										mysql_query($query);

										mysql_close();

										

										$recipient = "";

										$senderfrom = "Accord Vehicle Search Page";

										$mailheader = "From: \n";

										$message = "<adf>

													<prospect>

														<requestdate>0000-00-0000:00:00-00:00</requestdate>

														<vehicle interest=\"buy\" status=\"$New_Used\">

															<year>2009</year>

															<make>Honda</make>

															<model>Accord</model>

															<stock>NA</stock>

														</vehicle>

														<customer>

															<contact>

																<name part=\"first\">$first_name</name>

																<name part=\"last\">$last_name</name>

																<email>$email</email>

																<phone type=\"cellphone\" time=\"day\">$area-$prefix-$sufix</phone>

															</contact>

															<comments>Customer Comments: $comments</comments>

														</customer>

														<vendor>

															<contact>

																<name part=\"full\"> Accord Vehicle Search Page</name>

															</contact>

														</vendor>

														<provider>

															<name> Accord Vehicle Search Page</name>

														</provider>

													</prospect>

													</adf>";

										mail($recipient, $mailheader, $message);

											

										print "<table  border=\"0\" cellspacing=\"2\" cellpadding=\"2\">

										<tr valign=\"top\">

										  <td colspan=\"2\" align=\"left\" valign=\"top\"><font style=\"color:#006699; font-size:14px;\"><b>Your Inquiry Has Been Submitted</b><br><br><p class=\"copy\">Thank you $first_name $last_name, for inquiring about a new Honda Accord! We will respond to your message soon.<br><br>Thanks again and have a great day!</p>

										  <br><br>

										  </td>

										</tr>

									  </table>";

										}

									 

							

							

							print "</td>

						</tr>

						</table><br>";

				}

				else

				{

				

				do { 

				?>

              <table cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" style="width:495px; background-image: url(../images/inventory_line_bg.gif); background-position:bottom; background-repeat:repeat-x; padding-top:10px">

                  <tr>

                      <td rowspan="2" valign="top" style="padding-bottom:3px; padding-left:10px" width="25%">

                          <a href="accord-vehicle-details.php?Stock_No=<?php echo $row_CRV['Stock_No']; ?>&Trim=<?php echo $row_CRV['Trim']; ?>&Transmission=<?php echo $row_CRV['Transmission']; ?>&last_page=<?php print "colorado-accord.php"; ?>&price_q=<?php print "no"; ?>&Date_In_Stock=<?php echo $row_CRV['Date_In_Stock'];  ?>"><img src="<?php if ($row_CRV['Photo_URLs'] == "x") 

																				{

																				print "images/no_photo_1.jpg";

																				}

																			else 

																				{

																				echo $row_CRV['Photo_URLs']; 

																				}; ?>" width="120" height="97" border="0"></a>                    </td>

                    <td width="75%" rowspan="2" valign="top" style="padding-left:17px; text-align:left;" >

                      <a href="accord-vehicle-details.php?Stock_No=<?php echo $row_CRV['Stock_No']; ?>&Trim=<?php echo $row_CRV['Trim']; ?>&Transmission=<?php echo $row_CRV['Transmission']; ?>&last_page=<?php print "colorado-accord.php"; ?>&price_q=<?php print "no"; ?>&Date_In_Stock=<?php echo $row_CRV['Date_In_Stock'];  ?>" class="car_title">

                        <?php echo $row_CRV['Year']; 

						 print "&nbsp;";

						 echo $row_CRV['vehicle_trim']; 

						 ?>                      </a>

                    

                      <p class="copy_3">

                      Stock # :: <?php echo $row_CRV['Stock_No']; ?><br>

                      Exterior Color :: <?php echo $row_CRV['Ext_Color']; ?><br>

                      Interior Color :: <?php echo $row_CRV['Int_Color']; ?><br>

                      Transmission :: <?php echo $row_CRV['Transmission']; ?>

                      <br>Accessories :: <a href="javascript: popup_window('Accessories/nitrogen_tire_inflation.php',450,320)" class="map">Nitrogen Tire Inflation</a>

                      <?php if ($row_CRV['As_Name1'] != "")

													{

                      									print ", "; 

														echo $row_CRV['As_Name1'];

															

															if ($row_CRV['As_Name2'] != "")

															{

															print ", ";

															echo $row_CRV['As_Name2'];

															

																if ($row_CRV['As_Name3'] != "")

																{

																print ", ";

																echo $row_CRV['As_Name3'];

																

																	if ($row_CRV['As_Name4'] != "")

																	{

																	print ", ";

																	echo $row_CRV['As_Name4'];

																	

																		if ($row_CRV['As_Name5'] != "")

																		{

																		print ", ";

																		echo $row_CRV['As_Name5'];

																		

																			if ($row_CRV['As_Name6'] != "")

																			{

																			print ", ";

																			echo $row_CRV['As_Name6'];

																			

																				if ($row_CRV['As_Name7'] != "")

																				{

																				print ", ";

																				echo $row_CRV['As_Name7'];

																				

																					if ($row_CRV['As_Name8'] != "")

																					{

																					print ", ";

																					echo $row_CRV['As_Name8'];

																					

																							if ($row_CRV['As_Name9'] != "")

																							{

																							print ", ";

																							echo $row_CRV['As_Name9'];

																							

																								if ($row_CRV['As_Name10'] != "")

																								{

																								print ", ";

																								echo $row_CRV['As_Name10'];

																								}

																								else

																								{

																								}

																							}

																							else

																							{

																							}

																					

																					}

																					else

																					{

																					}

																				}

																				else

																				{

																				}

																			}

																			else

																			{

																			}

																		}

																		else

																		{

																		}

																	}

																	else

																	{

																	}

																}

																else

																{

																}

															}

															else

															{

															}

													

													}

													else

													{

													}

													?>

                      </p>

                      <a href="accord-vehicle-details.php?Stock_No=<?php echo $row_CRV['Stock_No']; ?>&Trim=<?php echo $row_CRV['Trim']; ?>&Transmission=<?php echo $row_CRV['Transmission']; ?>&last_page=<?php print "colorado-accord.php"; ?>&price_q=<?php print "no"; ?>&Date_In_Stock=<?php echo $row_CRV['Date_In_Stock'];  ?>" style="padding-left:10px" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image<?php echo $row_CRV['Stock_No']; ?>','','../images/vehicle_details_d.jpg',1)"><img src="../images/vehicle_details.jpg" alt="Accord Vehicle Details Page" name="Image<?php echo $row_CRV['Stock_No']; ?>" width="78" height="20" border="0" style="padding-left:15px; padding-top:7px; padding-bottom:5px"  align="left"></a>                    </td>

                    <td width="20%" nowrap style="padding-right:5px">

                      <p class="copy_5"><?php			

								if ($row_CRV['MSRP'] != "" ) 

								{

										

											

														print "MSRP: $";

														$number = $row_CRV['MSRP'];

														$add_com_format = number_format($number);

														echo $add_com_format;

														print "<br>Accessories: $";

														$Ass_total_A = (50 + $row_CRV['As_Retail_Price1'] + $row_CRV['As_Retail_Price2'] + $row_CRV['As_Retail_Price3'] + $row_CRV['As_Retail_Price4'] + $row_CRV['As_Retail_Price5'] + $row_CRV['As_Retail_Price6'] + $row_CRV['As_Retail_Price7'] + $row_CRV['As_Retail_Price8'] + $row_CRV['As_Retail_Price9'] + $row_CRV['As_Retail_Price10']);

														$Ass_total = number_format($Ass_total_A);

														echo $Ass_total;

														print "<br>Vehicle List Price: $";

														$V_total = ($number + $Ass_total_A);

														$V_total_A = number_format($V_total);

														echo $V_total_A;

										

														

								

													

										

									}

									else

									{

											print "<strong>Please Call for Price</strong>";
 

										}

										

								?></p>				    </td>

                  </tr>

                  <tr>

                    <td valign="bottom" style="padding-bottom:3px">

                      

                      

                      

                      <script type="text/javascript">
 

						function openn<?php echo $row_CRV['VIN_No']; ?>(){

							emailwindow=dhtmlmodal.open('EmailBox', 'iframe', 'zipcode.php?Stock_No=<?php echo $row_CRV['Stock_No']; ?>&Trim=<?php echo $row_CRV['Trim']; ?>&Transmission=<?php echo $row_CRV['Transmission']; ?>&last_page=<?php print "colorado-accord.php"; ?>&price_q=<?php print "yes"; ?>&Date_In_Stock=<?php echo $row_CRV['Date_In_Stock'];  ?>', '', 'width=330px,height=200px,center=1,resize=0,scrolling=0')

						

						

						} //End "opennewsletter" function

						

						</script>

                      <a href="#" onClick="openn<?php echo $row_CRV['VIN_No']; ?>(); return false" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image<?php echo $row_CRV['VIN_No']; ?>','','../images/get_your_price_d.jpg',1)"><img src="../images/get_your_price.jpg" alt="Get Your Price for this Accord" name="Image<?php echo $row_CRV['VIN_No']; ?>" width="106" height="65" border="0" align="right"></a></td>

                </tr>

              </table>

             <?php } while ($row_CRV = mysql_fetch_assoc($CRV)); }?>

                 <p class="copy" style="text-align:center"><?php if ($pageNum_CRV > 0) { // Show if not first page ?>

                <a href="<?php printf("%s?pageNum_CRV=%d%s", $currentPage, max(0, $pageNum_CRV - 1), $queryString_CRV.($Trim?'&Trim='.$Trim:'').($Body_Style?'&Body_Style='.$Body_Style:'').($Ex_Color?'&Ex_Color='.$Ex_Color:'')); ?>" class="bro"><< Previous Page</a>

                <?php } // Show if not first page ?>

             Showing <?php echo ($startRow_CRV + 1) ?> - <?php echo min($startRow_CRV + $maxRows_CRV, $totalRows_CRV) ?> of <?php echo $totalRows_CRV ?>

					New Honda <?php if ($Trim == "")

					{

										

					}

					else if ($Trim == "All")

					{

					

					}

					else 

					{

						echo $Trim;

					}

				?>

				Accords

				

              <?php if ($pageNum_CRV < $totalPages_CRV) { // Show if not last page ?>

                <a href="<?php printf("%s?pageNum_CRV=%d%s",  $currentPage, min($totalPages_CRV, $pageNum_CRV + 1), $queryString_CRV.($Trim?'&Trim='.$Trim:'').($Body_Style?'&Body_Style='.$Body_Style:'').($Ex_Color?'&Ex_Color='.$Ex_Color:'')); ?>"  class="bro">Next Page >></a>

                <?php } // Show if not last page ?></p>

                <!-- End Accord Inventory -->

Open in new window

0
Comment
Question by:Cutthroat_Trout
  • 7
  • 3
11 Comments
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24331984
It sounds like what you're wanting to do might be better accomplished in your SQL code... but let me make sure I understand exactly what you're wanting first. :-)

You've got your SQL query above, and presumably it returns a list of brand new white Honda Accords.  What is it you want to do next?  Is it:
  • display the details for each of these results (e.g. car #32 has leather interior and stereo; care #82 has a 6-disc changer)?
  • summarize the data based on a certain variable, such as trim (e.g. 3 cars with two-tone paint, 5 with pinstripes)?
  • something else I'm not thinking of?
If it's the first-- if you want to list cars individually in some way-- then you need to explain what you mean by avoiding duplicate vehicle_trim values... unless you're looking for some sort of limited result set (in other words, not every car that matches Accord/new/white.

If it's the second-- summarizing or grouping based on vehicle_trim-- then no problem, I can give you some SQL examples.

Otherwise, let me know what you want. :-)
0
 
LVL 7

Expert Comment

by:ycTIN
ID: 24332549
http://dev.mysql.com/doc/refman/5.0/en/select.html
$query_CRV = "SELECT *

FROM ebizcardata

INNER JOIN Vehicle_Additional_Information

ON ebizcardata.Stock_No = Vehicle_Additional_Information.Stock_No

WHERE Modell = 'Accord'

AND New_Used = 'New'

AND Ext_Color

IN ('White Diamond Pearl', 'Taffeta White')

GROUP BY vehicle_trim

ORDER BY Year ASC , STR_TO_DATE( Date_In_Stock, '%m/%d/%Y' ) ASC ";

Open in new window

0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24332608
What ycTIN has posted is close to what I'm suggesting in my summary option (option #2).  However, depending on what fields you're pulling in your SELECT statement, you may get an error just adding that GROUP BY clause as is.

When you do a GROUP BY, then every field you pull in a SELECT statement must either be within a summary function or listed in the GROUP BY clause.  Say, for example, you had four fields called field1 through field 4.  You'd have to do something like:

SELECT field1, field2, SUM(field3), MAX(field4)
FROM myTable
GROUP BY field1, field2

If you tried to run:

SELECT field1, field2, field3
FROM myTable
GROUP BY field1

you would likely get an error.


0
 

Author Comment

by:Cutthroat_Trout
ID: 24356913
Thanks for you attempts but Group By will not work. Each record is unique and I can not just group records.

OK for a slimed down version of what I am trying to do is this.

Table 1 Car_Data - has five fields car_color, car_stock_no, car_sale_price, car_date_in_stock, car_photos
Tabe 2 Car_Data_Two has four fields car_stock_no, car_accessories, car_MSRP, car_trim  

My statement needs all the information form both tables joining on car_stock_no and all vehicles selected need to be white. So there are then 10 cars returned and they are all white. but 4 are EX tirm Level, 1 is an LX trim and 5 are EX-L trim. But the results need to NOT show the duplicated trims. The results should only show 3 cars one EX, one LX and one EX-L and these three vehicles should be the oldest car_date_in_stock.  
0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24357025
Okay, so what you're saying is you want a resultset like this:

Of the white cars in stock, we currently offer 3 trim styles:
  • EX
  • LX
  • EX-L
And you don't tell them how many of each.  Is that right?  If so, here's the SQL you're looking for:

SELECT vehicle_trim, MIN(Date_In_Stock) as minDate
FROM ebizcardata
INNER JOIN Vehicle_Additional_Information
ON ebizcardata.Stock_No = Vehicle_Additional_Information.Stock_No
WHERE Modell = 'Accord'
AND New_Used = 'New'
AND Ext_Color IN ('White Diamond Pearl', 'Taffeta White')
GROUP BY vehicle_trim
ORDER BY minDate ASC


0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:Cutthroat_Trout
ID: 24357198
But each car has unique data, price, details, accessories and so on. So I need all the data from both tables for each individual vehicle (stock number) but when I pull results I only want to show one of each trim level.

As soon as I do a group by it just groups all the data for all the vehicles with that trim.  
0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24357215
Right... so you're saying that for all cars matching the white Honda criteria, you want to pull exactly one match for each type of trim.  So if there are five white Hondas with LX trim, you only want the one that's been in stock longest.

Am I getting that right?
0
 
LVL 8

Accepted Solution

by:
Bobaran98 earned 500 total points
ID: 24357247
If so, try this code on for size:

SELECT DISTINCT *
FROM ebizcardata A
INNER JOIN Vehicle_Additional_Information B
ON A.Stock_No = B.Stock_No
WHERE Stock_No = (
SELECT TOP 1 Stock_No
FROM ebizcardata
INNER JOIN Vehicle_Additional_Information
WHERE
vehicle_trim = A.vehicle_trim
AND Modell = 'Accord'
AND New_Used = 'New'
AND Ext_Color IN ('White Diamond Pearl', 'Taffeta White')
ORDER BY Date_In_Stock ASC
)

0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24357302
A few notes on the code I just posted:
  • I'm not sure how familiar you are with SQL, but the 'A' and 'B' are aliases for the table names... when you throw them after the table name in the FROM clause, you can then use them later in place of the table name
  • Therefore, even though it appears in the subquery, A.vehicle_trim still refers to vehicle_trim in the outer query, which is important for matching things up properly; however, I'm not sure which table vehicle_trim is part of... if it's Vehicle_Additional_Information, then you'll need to change A.vehicle_trim in the subquery to B.vehicle_trim.
  • If you don't use DISTINCT in your outer query, and say there are 5 white Hondas with EX, 3 with LX, and 2 with EX-L, you'll end up with ten results... five identical results showing the oldest car with EX, three identical of the oldest LX, and two identical of the oldest EX-L.  The distinct removes those duplicates.
  • Finally, explanations aside, you may still need to clean up what I just gave you... the table names and field names you provided in your post at 1:31 Eastern don't match the names you've been using in your code throughout the rest of this discussion... so I'm really not sure what's correct!
0
 
LVL 8

Expert Comment

by:Bobaran98
ID: 24357332
By the way, why do you have two separate tales?  I assume the stock number is the unique ID (in other words, every car in your system has its own different stock number).  Am I inferring correctly that every car in your system has exactly one entry in both tables?  If so, you'd save yourself a lot of work if you combine the tables into one.

On the other hand, if perhaps every car in your system has an entry in ebizcardata, but for only some of the cars you add a record about additional options in the Vehicle_Additional_Information table... then your current setup is probably fine.

And if you're an old hat at SQL, forgive me making these observations.  Just trying to help the best I can! :-)
0
 

Author Closing Comment

by:Cutthroat_Trout
ID: 31579137
Thanks for your help! Have a great day!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
spacing 5 30
How do I edit this Drupal page? 9 30
How to display a functions data inside HEREDOC 2 14
wordpress issue 2 20
Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now