[SOLVED] mysql query - checking if row is in another table

[daveh33]

I have the below script which is used to export a batch of numbers from a mysql database.

 

There are 2 tables I have, numbers & STOPS - what I want to do is remove any of the rows in the numbers table if it appears in the STOPS table.

 

The below code i am using - but it doesn't seem to remove the STOPS

 

$result=mysql_query("SELECT DISTINCT(number) FROM numbers LIMIT $start,$finish");
while ($row = mysql_fetch_array($result)) {
$number = $row['number'];
$result1 = mysql_query("SELECT DISTINCT(number) FROM STOPS") or die(mysql_error());
$row1 = mysql_fetch_array($result1);
$stop = $row1['number'];
if ($number!=$stop) {
$number1="$number \n";
fwrite($fh, $number1);
}
}
fclose($fh);

[Sulman]

Have a look at this:

 

$result=mysql_query("select number from STOP");
$where="";
while ($row = mysql_fetch_array($result)) 
{
//build the query string
$where.=$row['number']." OR number=";
}

//you'll need to add some code to remove the last "OR number="

$result=mysql_query("delete from number where number=".$where);

etc...

 

I haven't tested this and it'll probably need a little work but you get the idea...

 

[daveh33]

Would this code delete it from the number table? As that wasn't what I wanted - I just wanted it not to write it to the file

[aschk]

So you want all the numbers from the numbers table that aren't in the STOPS table.

You can do this in 1 SQL query.

 

SELECT number 
FROM  numbers 
WHERE number NOT IN (
    SELECT number 
    FROM STOPS 
    GROUP BY number
) GROUP BY number

 

Hope that helps...

[daveh33]

Ah I didn't know you could do a query like that - I do get an error thought

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE number NOT IN ( SELECT number FROM STOPS GROUP BY number ) G' at line 3

[aschk]

That works fine for me. Here is my test data:

 

CREATE TABLE numbers (
  number INT UNSIGNED
);

CREATE TABLE STOPS (
  number INT UNSIGNED
);

insert into numbers values(12),(3),(5),(21),
(35),(6),(7),(34),(12),(432),(4525),(4),(67),(66),(23),(23),(23),(6);

insert into STOPS values(12),(3),(5),(21),
(35),(6),(7),(34);

SELECT number
FROM  numbers 
WHERE number NOT IN (
    SELECT number 
    FROM STOPS 
    GROUP BY number
) GROUP BY number;

[aschk]

Thus, what version of MySQL are you using?

[daveh33]

Version: MySQL v5

 

I have edited it slightly: -

 

$result=mysql_query("SELECT DISTINCT(number)
FROM  $tablename LIMIT $start,$finish
WHERE number NOT IN (
    SELECT DISTINCT(number) 
    FROM STOPS 
    GROUP BY number
) GROUP BY number") or die(mysql_error());

[aschk]

EURGH, no wonder it doesn't work. You can't embed LIMIT statements like that.

That, and you don't need to use them (because otherwise you're limiting your results).

LIMIT ALWAYS comes at the end of the statement.

Also, DON'T use Distinct, it's bad practise, because I don't think you understand what it actually does, nor what the GROUP BY does.

Here is an alternate statement to try (despite the fact I know my first statement works)

 

SELECT n.number
FROM  numbers n
LEFT JOIN STOPS s ON s.number = n.number
WHERE s.number IS NULL
GROUP BY n.number;

 

If you INSIST on limiting your results this is how you do it:

 

$result=mysql_query("SELECT number
FROM  $tablename 
WHERE number NOT IN (
    SELECT number
    FROM STOPS 
    GROUP BY number
) GROUP BY number
LIMIT $start,$finish") or die(mysql_error());

[daveh33]

That second statement works just fine - the reason I have to limit the results is because I have a 500,000 records in the table & the script is used to export chunks of usually 10k.

 

Distinct was used (I thought!) to remove the duplicates.

 

Thanks for your help

[aschk]

DISTINCT, in my personal opinion is a bad bad MySQL component and is generally used when people are unsure how to get unique values out of their database. It works ok for you (for the time being) because you're using it on 1 column, however if you suddenly decided to get 2 columns out of your table and used DISTINCT(number) you would find that your 2nd column may contain spurious results.

Limiting is fine, just make sure to put it at the end of the WHOLE sql statement for it to work.

[daveh33]

Ok thanks for your input - much appreciated