Hi, try this:

SELECT s.id_number from serial s, newbuild n WHERE s.id_number <> n.id_number

It should give you some idea on how it's being selected from multiple tables.

A LEFT OUTER JOIN is your friend in this case. :)

SELECT Serial.ID_Number, Serial.Serial
FROM Serial LEFT OUTER JOIN Newbuild ON Serial.Serial=Newbuild.Serial
WHERE Newbuild.Serial IS NULL

Oops! Sorry guys, didn't realise you'd replied whilst I was mid edit.

I'll have a play with the suggestions and see how we get on!

Thanks,
MRb

Change to:
SELECT Serial.ID_Number, Serial.Serial
FROM Serial LEFT OUTER JOIN Newbuild ON Serial.ID_Number=Newbuild.ID_Number
WHERE Newbuild.ID_Number IS NULL

Hi there LifeInAsia,

Thanks for the update there, this query is VERY slow and doesn't seem to do as I expect. I see the method of use there, and happy as it looks like it can be done.

From the first example I gave, the result should be 1 serial number - the one that's NOT used in the new build table.

I'm trying a few things, and if I get any success I'll keep you informed.

Cheers,
MRb

this query is VERY slow

have you created indexes on the ID_Number and Serial columns?

this query is VERY slow and doesn't seem to do as I expect.

As phranque said, make sure you're properly indexed. Also, is ID_number an INT field or VARCHAR? (Searching on INTs is almost always faster than VARCHARs.) Also, how many rows are in each table?

@Phranque: I'm checking, but the indexes are on the id and serial - and yes they are both primary key, this is the same on both tables.

@lifeInAsia: Yeah, checked over that too; id_number for both tables is int, and serial is varchar.

There are around 5000 rows on each table, so there is bound to be some delay as each entry is being evaluated, I appreciate that.

My intention is to be able to select from the serial table ONLY serial numbers that aren't in use yet.

Thanks as always for the continued support.

Cheers,
MRb

yes they are both primary key

You mean the indexes on id and serial are BOTH primary keys? Um, I'm surprised this is even possible- it violates the purpose of a *primary* key.

around 5000 rows on each table, so there is bound to be some delay as each entry is being evaluated

This is exactly what DBs are designed to do- comparing 2 5,000-row tables should be a trivial task for any DB. We regularly join tables with millions of rows, generating a result set in seconds at the most.

Hi there LifeInAsia,

I've double & triple checked (I'm using HeidiSql GUI) and it tells me that I have successfully got two primary keys - one on the ID_number and one on the SerialNumber (newbuild table & one primary key on ID_Number on the serial table).

Ok, that makes me more confident on my given task then. I've only every dealt with tables of this sort of size..

I've tried to sort the query out and I've understood how this functions with the OUTER JOIN - essentially creating/merging the two tables on the serial numbers, the the ones where the column in the WHERE clause returns NULL, the serial number hasn't been included. Is my understanding of this correct?

I can't get the query to return what it should do, however, the query HAS sped up since doing the indexing properly, I'd missed one off the newbuild table.

I'll look at this more on Monday.

Thank you for the continued help.

Cheers,
MRb

got two primary keys

Are you sure it says 2 primary keys and not 2 indexes?

Is my understanding of this correct?

Correct. If you're having trouble visualizing things, go back to the Venn diagrams [en.wikipedia.org] that you probably learned in school and wondered how on Earth you would ever use them again in the real world. :) Working with DBs is basically set theory at its core (think of each table as a separate set of objects).

Hi there LifeInAsia,

I can't explain it then; I agree with you that 'Primary' index (with a key symbol next to it) is by definition singular & not plural - but my MySql GUI tells me that under indexes, I have two Primary Keys, I wish that I could show you a screengrab of this, but I can't.. Oh well.

Well I pleased that I got something correct! And thanks for the Venn pointer, I never realised the connection before, and it's be a LONG while since I did that at school.

As I say, I'll ponder this a little more on Monday, I'm not really on any time constraints for completing this project now. Just when a manager says 'Can you just...', it ususally means a lot of un-necessary work that will probably be forgotten by the time he wanders off!

Thank you for the continued help anyway,

Cheers,
MRb