比较3个表并创建价格最低的新表

Hallo i have created 3 tables in my DB.

Table A = distributor A

Table B = distributor B

Table C = distributor C

Each table are updated with the following fields from each distributor.

Sku, Price, Stock, product name, name of distributor

What I now want to do is to create a new table where prices ready for import should be.

There is the following rules:

If stock = 0 skip

if same sku is in all 3 distributor table, take lowest price and update with lowest price.

So i the have a new table containing the following:

Sku, price, stock,product name, name of distributor.

Above table only contain each sku onces, and always lowest price with positive stock.

Anybody :-)

× 96472

225296

Your question isn't the best, I'll try to help but better questions get better answers.

I'll assume you need to keep the tables 3 separate tables (I personally wouldn't, I'd just add a distributor field or similar and put everything in one table).

(Judging by your PHP tag, I'll put it in php).
I'll provide a rather specific example, but it is important that you understand how it works so you can adapt it.

//Dist. A
$query = "SELECT * FROM `db`.`table_a` WHERE 1 ORDER BY `sku` DESC"; //Get the list of products
$result = mysql_query ($query) or die (mysql_error());
while ( $row = mysql_fetch_assoc($result)) {
  if ( $row['stock'] != 0 ) { //If we have stock...
    $query = "SELECT * FROM `final_table` WHERE `sku` LIKE '".$row['sku']."'"; //See if we have the data in the final table
    $final_result = mysql_query ($query) or die (mysql_error());
    if ( mysql_num_rows($final_result) == 0 ) { //Product Not in new table
      $query = "INSERT INTO `final_table` VALUES ( '".$row['sku']."' [The rest of your values]";
      mysql_query ($query) or die (mysql_error());
    }
    if ( mysql_num_rows($final_result) >= 1 ) { //Product In new table
      $new_row = mysql_fetch_assoc($new_result); //Get the data into an array so we can compare the prices
      if ( $row['price'] < $final_row['price'] ) { //New price is lower
        $query = "UPDATE `final_table` SET `dist` = 'A', `price` = '".$row['price']."' [Other Vars that need update] WHERE `sku` LIKE '"$row['sku']"'";
        mysql_query ($query) or die (mysql_error());
      }
    }
  }
}

Then repeat it for B and C tables, updating the table names and the value that's put in the final db.

(I realize I probably violated some conventions in that example, but the concept is there.)

This does assume a few things, mainly that sku is some sort of unique index.