homepage Welcome to WebmasterWorld Guest from 54.234.147.84
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Some advice with a query please
Matthew1980




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

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




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

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




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

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




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

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




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

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




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

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




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

this query is VERY slow


have you created indexes on the ID_Number and Serial columns?

LifeinAsia




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

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




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

@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




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

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




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

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




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved