INNER JOIN problems

[lansing]

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

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

[code=php:0] 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>
[/code]

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. [code=php:0]<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>[/code]

[emehrkay]

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

[btherl]

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:

[code]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[/code]

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.

[lansing]

[quote author=btherl link=topic=107192.msg429690#msg429690 date=1157598105]
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:

[code]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[/code]

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.
[/quote]Thanks...works great.