Welcome to WebmasterWorld Guest from 54.226.246.160

Forum Moderators: open

Message Too Old, No Replies

Some advice with a query please

     

Matthew1980

10:22 pm on Mar 13, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



Hi there guru's

*Psudo Code*

SELECT `serial`.`SerialNumber`, `serial`.`ID_Number` FROM `Serial`, `newbuild` WHERE `serial`.`id_number` not IN `newbuild`.`serial` ORDER BY `date` DESC

Essentially I want to select ONLY values that DON'T appear in the other table:-


Serial Number table

ID_number | Serial
1----------we2345
2----------we2346
3----------we2347
4----------we2348

Newbuild table

ID_number | Serial
1-----------2
2-----------1
3-----------3

So for this mock table, I would only want ID_number 4 to be selected from serial table as the rest are already allocated.

Thanks for any thoughts/suggestions.

Cheers,
MRb

[edited by: Matthew1980 at 10:56 pm (utc) on Mar 13, 2013]

matrix_jan

10:30 pm on Mar 13, 2013 (gmt 0)

5+ Year Member



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.

LifeinAsia

10:32 pm on Mar 13, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

Matthew1980

10:57 pm on Mar 13, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

LifeinAsia

11:05 pm on Mar 13, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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

Matthew1980

10:05 am on Mar 14, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

phranque

10:53 am on Mar 14, 2013 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



this query is VERY slow


have you created indexes on the ID_Number and Serial columns?

LifeinAsia

4:24 pm on Mar 14, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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?

Matthew1980

7:01 pm on Mar 14, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



@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

LifeinAsia

8:01 pm on Mar 14, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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.

Matthew1980

10:51 pm on Mar 15, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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

LifeinAsia

11:15 pm on Mar 15, 2013 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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).

Matthew1980

10:33 am on Mar 16, 2013 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month