two tables mistake

zeberrun

i am blind any more cant see please help me.

tablea
product_address (numeric 1 to 5000),
products_id

tableb
product_def_address (numeric 1 to 5000),
products_id

tables are broken, tablea and tableb must same products_id.
i want to find products_id which exists in tableb but not in tablea.
the result must show the tablea's products_id.

select from table a as a, table b as b where a.products_id != b.products_id;
and
select from table a as a, table b as b where product_address != b.product_def_address and a.products_id != b.products_id;

best regards

bradgrafelman

I don't understand your question:

zeberrun wrote:
i want to find products_id which exists in tableb but not in tablea.
the result must show the tablea's products_id

That doesn't make any sense; if a products_id value exists in tableb but not in tablea, then there is no 'products_id' value from tablea to show.

zeberrun

tablea
product_address (numeric 1 to 5000),
products_id

tableb
product_def_address (numeric 1 to 5000),
products_id

tables are broken, tablea and tableb must same products_id.
i want to find products_id which exists in tableb but not in tablea.
the result must show the tablea's products_id.

select * from table a as a, table b as b where a.products_id != b.products_id;

pls check in this way.

bradgrafelman

I already told you that your original question was unclear, why copy and paste the same unintelligible text again?

I still don't understand how a row in tableb is supposed to be related to a row in tablea if their products_id values don't match.

zeberrun

dear friend,

when the tables were created they were equal.
tablea had 200 lines(products_id), tableb had 200 lines(products_id), they were connected to each other with product_id numbers. after a while some of the lines were deleted from tablea mistakenly and connection brake.

now i want to find the deleted lines in tablea, and insert them manually in order to be worked tables properly.

products_id value is unique. if i find the not equal products_id, i tried the version of code written behind but i miss something.

tablea
product_address (numeric 1 to 5000),
products_id

tableb
product_def_address (numeric 1 to 5000),
products_id

select * from table a as a, table b as b where a.products_id != b.products_id;

DaveRandom

This would result in a lot of querys, and there is probably a more efficient way of doing it, but since it's a one-time job it wouldn't really matter.

If I understand you right, you could do something like this...

  $pos = 0;
  $break = 30;

  while (mysql_num_rows($result = mysql_query("SELECT * FROM `table_a` LIMIT $pos,$break")) > 0) {
    while ($row = mysql_fetch_array($result)) {
      $exists = mysql_fetch_array(mysql_query("SELECT count(*) AS 'exists' FROM `table_b` WHERE `products_id` = '".$row['products_id']."'"));
      if (!$exists['exists']) {
        mysql_query("INSERT INTO `table_b` (`products_id`) VALUES ('".$row['products_id']."')");
      }
    }
    $pos += $break;
  }

This assumes that product_def_address in table_b is an auto-incrementing primary key.

zeberrun

dear dave,
sorry for keeping r valuable time, but i am not familiar with php.
i just want to receive results from phpmayadmin window, not to use php code.
i just to see which products (product_id) are not in tablea.

regards

DaveRandom

SELECT `tablea`.`products_id` 
FROM `tableb` 
RIGHT OUTER JOIN `tablea` ON `tableb`.`products_id` = `tablea`.`products_id` 
WHERE `tableb`.`products_id` IS NULL

Borrowed from here