...

View Full Version : Odd MySQL query question



lansing
08-25-2006, 06:20 AM
I don't know if this is possible, but I will get straight to the point.

I have my query pulling data from TableA. I am using Comparison Operators to get the data that I need for this page. The only column pertaining to this problem is the record_id in this table. I need to pull data from TableA with the comparison of if the status of this in TableB is a certain status.

TableA has the record_id
TableB has status of either No or Yes

A longer explanation of what I need. I need to select the record_id from TableA where the status, which is in TableB, is No. I can't move or add a column to TableA to house the status. The status for every record is in another Table, but I still need to be able to pull records based on the status of that same id from another table.

guelphdad
08-25-2006, 06:37 AM
so what does this have to do with PHP? it should be posted in the MySQL area.

Do you also have a column for record_id in table B? If so then how do you think you should be performing the join to get the query you want?

lansing
08-25-2006, 08:12 AM
so what does this have to do with PHP? it should be posted in the MySQL area.I didn't specify, but I am using PHP to query the db...I do not have shell access.



Do you also have a column for record_id in table B? If so then how do you think you should be performing the join to get the query you want?I do have record_id in table B. I do not know how to get the query I need & that is why I am asking. I have just been listing all records on a page w/the status shown. I want to change the page & show only the records that the status is No w/out showing the status.

I have like 3 Table Columns on the page. Column 1 showed record_id, Column 2 showed record_name, & Column 3 showed status. With this we would do what we needed to do with the data.

I want to change this & have 2 Table Columns w/only the record_name & record_id, but only need to display records where the status in Table B is No.

This is what I have so far w/out the needed change.

$query_seminars = ("SELECT record_name, record_id
FROM $tableA
WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= record_date
AND record_name != ''
ORDER BY `record_id` DESC");

GJay
08-25-2006, 08:56 AM
can you provide your table schema? your explanation was confusing

lansing
08-25-2006, 03:33 PM
CREATE TABLE `tableA` (
`record_id` int(32) NOT NULL auto_increment,
`record_name` varchar(32) NOT NULL default '0',
PRIMARY KEY (`record_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Order Base' AUTO_INCREMENT=13830 ;


CREATE TABLE `tableB` (
`commission_id` int(32) NOT NULL auto_increment,
`regth_id` int(32) NOT NULL default '0',
`reth_id` int(32) NOT NULL default '0',
`record_id` int(32) NOT NULL default '0',
`commission` decimal(6,2) NOT NULL default '0.00',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`status` enum('NO','YES') NOT NULL default 'NO',
PRIMARY KEY (`commission_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Commissions Archives' AUTO_INCREMENT=13045 ;

The record_id in tableB is the same number that is the record_id in tableA. I need to pull that record_id & record_name from tableA ONLY when the status in tableB is NO.

GJay
08-25-2006, 08:26 PM
SELECT a.record_id, a.record_name FROM tablea a JOIN tableb b ON (a.record_id=b.record_id) WHERE b.status='NO';

lansing
09-03-2006, 04:08 AM
I have to make this work again, but it needs to be a little different.

I need to pull that record_id from tableA ONLY when the record_id is found in tableB. Like if record_id number 1050 is not in tableB then don't need that record's data from tableA.

GJay
09-03-2006, 10:13 AM
SELECT a.record_id, a.record_name FROM tablea a JOIN tableb b ON (a.record_id=b.record_id)

lansing
09-03-2006, 02:17 PM
Thanks. It pulls the records like I need, but for some reason it pulls them twice. I have posted the php code below. I am copying & pasting it from my php file. I am using a code that I found to show records in a drop down menue & once you choose an option it populates the page with the record info.

I did change the PHP Comparison Operator to != in the JOIN statement & it pulls the records like I need. I manually checked all the records displayed in the drop down menu & it is correct.

The problem with showing each record twice is that in the drop down menu it is showing each record_id twice & not just once.

<?PHP
//error_reporting(E_ALL);
require_once('cdb.php');

mysql_select_db($db_name, $conn);
//SELECT a.record_id, a.record_name FROM tablea a JOIN tableb b ON (a.record_id=b.record_id)
$query_seminars = ("SELECT a.customers_id, a.order_id, a.order_date, a.referring_id, a.order_status
FROM $order_table a 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>

GJay
09-03-2006, 03:30 PM
your query isn't the one I gave...joining with a != condition isn't what you want- I can't think of a situation where you would ever want that.

doing ...FROM table a JOIN table b...
is the same as:
...FROM table a LEFT INNER JOIN table b...
(LEFT and INNER are the defaults)
this means that the query will only take records from a that meet the join condition, which is what you said you wanted.

lansing
09-03-2006, 07:35 PM
With me just using FROM $order_table a JOIN $commisions_table b ON (a.order_id=b.order_id) it is pulling the records that are in the $commisions_table. I emptied the tables except for 5 records. I placed the record_id for 2 records in the $commisions_table. In the drop down menu it is showing those record's id numbers. I need it to show every record's id number that is in the $order_table, but not yet in the $commisions_table

When I used the != it showed me every record_id that was in $order_table that hasn't yet been added to $commisions_table which is what I need.

lansing
09-05-2006, 11:39 PM
Don't mean to bump my own thread, but I would like a little more help since I got it working, but it is showing the Record ID's twice in the drop down menu.

I have to have this completed within 48 hours so that I don't have to make monthly ratings by hand in Excel for last month.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum