Jump to content


Photo

INNER JOIN problems


  • Please log in to reply
3 replies to this topic

#1 lansing

lansing
  • Members
  • PipPip
  • Member
  • 15 posts
  • LocationClinton, TN USA

Posted 07 September 2006 - 02:41 AM

I have TableA & TableB. I am quering TableA for records, but only want to pull record data if the record_id from TableA is in TableB. I have posted other places & came to the conclusion that I need to use INNER JOIN to accomplish what I need. I have posted what I have below, but this code keeps pulling data from TableB & not TableA.

To fully explain this the first time. I am taking the records from TableA that are NOT in TableB & displaying them in a drop down menu. I then use this data on my page.

mysql_select_db($db_name, $conn);
		
		$query_seminars = ("SELECT a.customers_id, a.order_id, a.order_date, a.referring_id, a.order_status 
							FROM $order_table a INNER JOIN $commisions_table b ON (a.order_id=b.order_id)
							WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= a.order_date 
							AND a.referring_id != ''
							AND a.order_status = 'Filled'
							ORDER BY a.order_id DESC");
		$seminars = mysql_query($query_seminars, $conn) or die(mysql_error());
		$row_seminars = mysql_fetch_assoc($seminars);
		$totalRows_seminars = mysql_num_rows($seminars);
		
		$colname_chosen_seminar = "-1";
		if (isset($_POST['order_id'])) {
		  $colname_chosen_seminar = (get_magic_quotes_gpc()) ? $_POST['order_id'] : addslashes($_POST['order_id']);
		}
		mysql_select_db($db_name, $conn);
		$query_chosen_seminar = sprintf("	SELECT * 
											FROM $order_table 
											WHERE order_id = %s", $colname_chosen_seminar);
		$chosen_seminar = mysql_query($query_chosen_seminar, $conn) or die(mysql_error());
		$row_chosen_seminar = mysql_fetch_assoc($chosen_seminar);
		$totalRows_chosen_seminar = mysql_num_rows($chosen_seminar);
	?>
<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post" name="news_entry" id="news_entry">
  <table width="40%" border="0" align="center" cellpadding="4" cellspacing="0" class="newsheader">
    <tr> 
      <td width="44%" align="left" valign="top"><div align="right"><strong>Order ID :</strong></div></td>
      <td width="56%" align="left" valign="top"><div align="left">
        <select name="order_id" onChange="chkFrm(this)">
            <option value="Choose" <?php if (!(strcmp("Choose", $_POST['customers_id']))) {echo "SELECTED";} ?>>Select Order ID</option>
            <?php
do {
?>
            <option value="<?php echo $row_seminars['order_id']?>"<?php if (!(strcmp($row_seminars['order_id'], $_POST['order_id']))) {echo "SELECTED";} ?>><?php echo $row_seminars['order_id']?></option>
            <?php
} while ($row_seminars = mysql_fetch_assoc($seminars));
  $rows = mysql_num_rows($seminars);
  if($rows > 0) {
      mysql_data_seek($seminars, 0);
      $row_seminars = mysql_fetch_assoc($seminars);
  }
?>
        </select>

I know that on the line that displays the order_id in the drop down menu it is pulling it from the first query with should make that line be like the the line below, but if I make it like that then it just shows blank drop down menu.
<option value="<?php echo $row_seminars['a.order_id']?>"<?php if (!(strcmp($row_seminars['a.order_id'], $_POST['order_id']))) {echo "SELECTED";} ?>><?php echo $row_seminars['a.order_id']?></option>


#2 emehrkay

emehrkay
  • Staff Alumni
  • Advanced Member
  • 1,214 posts

Posted 07 September 2006 - 03:00 AM

while ($row_seminars = mysql_fetch_assoc($seminars));
  $rows = mysql_num_rows($seminars);
  if($rows > 0) {

this is kinda redundant and why do it when you already have

$row_seminars = mysql_fetch_assoc($seminars);

print_r($row_seminars) to see what is being passed

#3 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 07 September 2006 - 03:01 AM

Lansing, do you want entries which are in both Table A and Table B, or entries which are in Table A and NOT in Table B?

If you want entries in Table A and Table B, then inner join will work.  If you want entries in Table A but NOT in Table B, then you need to use this:

SELECT a.*
  FROM $order_table a LEFT OUTER JOIN $commisions_table b ON (a.user_id = b.user_id)
  WHERE b.user_id IS NULL

This will give you all rows from Table A which does NOT match a row from Table B.  This is because an outer join puts NULL in the b.user_id (and all columns from b) if no row from b was found.

#4 lansing

lansing
  • Members
  • PipPip
  • Member
  • 15 posts
  • LocationClinton, TN USA

Posted 07 September 2006 - 03:09 PM

Lansing, do you want entries which are in both Table A and Table B, or entries which are in Table A and NOT in Table B?

If you want entries in Table A and Table B, then inner join will work.  If you want entries in Table A but NOT in Table B, then you need to use this:

SELECT a.*
  FROM $order_table a LEFT OUTER JOIN $commisions_table b ON (a.user_id = b.user_id)
  WHERE b.user_id IS NULL

This will give you all rows from Table A which does NOT match a row from Table B.  This is because an outer join puts NULL in the b.user_id (and all columns from b) if no row from b was found.

Thanks...works great.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users