Jump to content

INNER JOIN problems


lansing

Recommended Posts

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.