homepage Welcome to WebmasterWorld Guest from 54.145.183.190
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
I can't see the wood for the trees.
Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4267598 posted 9:36 pm on Feb 15, 2011 (gmt 0)

Hello people,

SELECT DISTINCT `FaultType`, `ID` FROM `faultdatabase`

This isn't working as I expect, I shouldn't get duplicates from the FaultType column, and I would have expected the corresponding/associated id numbers to have been returned too, but I get:

(faulttype|id)

Short circuit 41
No power 40
Short circuit 42
No power 43

Whereas I would have expected this:-

(faulttype|id)
Short circuit 41
No power 40

As I would expect the lower numbers (first rows) to have taken precedence..

This will probably make sense tomorrow, but I just thought as I ought to seek advice.

Cheers,
MRb

 

jecasc

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4267598 posted 9:52 pm on Feb 15, 2011 (gmt 0)

I think what your statement does is return all unique COMBINATIONS of FaultType and ID.

Actually I think you can't limit DISTINCT to one of the columns in your SELECT query.

[edited by: jecasc at 9:53 pm (utc) on Feb 15, 2011]

johnblack



 
Msg#: 4267598 posted 9:52 pm on Feb 15, 2011 (gmt 0)

The distinct command applies across all columns - i.e. 'Short Circuit 41' and 'Short Circuit 42' are not dups.

It's effectively combining the columns to work out if there is a dup so if there were two rows

Short Circuit 44
Short Circuit 44

the query would only return one row for these two rows in the table.

Your second column is called ID, is this the primary key on the table? If so, then you will never get any dups as this field is unique across all rows on the table.

May be you are after the query

SELECT DISTINCT `FaultType` FROM `faultdatabase`

which would list all the different Fault Types in the table?

Hope that makes sense, haven't had a coffee yet :)

Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4267598 posted 10:17 pm on Feb 15, 2011 (gmt 0)

Hi there,

thanks for the clarity, I have just gone cross eyed trying to understand what the manual says about using DISTINCT in a conditional query, I'm more confused now..

Ideally I would like the column 'faulttype' to return NO duplicates, and for the ID's associated with the returned data to remain intact - this is for a VB.net ADODB recordset project for work, and when your populating a combobox, part of the object requires an index('id') so that when it's selected, there is a value there.

I shall try a few more combinations, but 10:20PM I think I am going to call it a night. I need caffine & food.

[EDIT] Ok, I have commented out the index object from the loop, and I shall see if I can get away with passing it as a string value & not an integer, hopefully this won't fudge things up too much...

So close to a working 'prototype' version...

Cheers for the advice anyway,
MRb

johnblack



 
Msg#: 4267598 posted 6:20 am on Feb 16, 2011 (gmt 0)

Hi MRb,

I may have got the wrong end of the stick but it sounds as if 'faultdatabase' in your opening post is table of faults which have a specific 'faulttype' i.e. One fault type can be applied to many faults.

If you are trying to populate a combobox from which the user selects a fault type for a given fault, then

Select distinct faulttype from faultdatabase is what you are after.

With regard to the id on the combobox, some databases would use a separate table to hold the distinct fault types, call it 'faulttypes' with a primary key of 'faulttypes_id'. So to populate the combobox you would use the query

Select faulttypes_desc, faulttypes_id from faulttypes

where the 'id' on the combobox is faulttypes_id.

Back on the fault table, instead of a 'faulttype' field there would be a foreign key to the faulttypes table, say 'faulttypes_fk_id'

The idea is that the combobox provides an 'id' for the faulttypes table which is stored in the faulttypes_fk_id column when the fault is updated or created by the application.

Hope that all makes sense!

Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4267598 posted 9:15 am on Feb 16, 2011 (gmt 0)

Hi there JohnBlack,

Thanks for the explanation, that makes sense to me, and realistically I should get the architecture of the table sorted out before I commit to a completed schema.

Thanks for the ideas, and I shall update when all is well.

Cheers,
MRb

Matthew1980

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4267598 posted 8:57 pm on Feb 16, 2011 (gmt 0)

Hi all,

Well, diligence and lots of coffee have paid off, this is now working the way I wanted it to. I have split the query into sections depending on how the selections were made, and now it works exactly as I intended it to, though the idea of sorting out the schema will more than likely be included in the next version of this software.

Thank you for the help.

Cheers,
MRb

johnblack



 
Msg#: 4267598 posted 10:25 pm on Feb 16, 2011 (gmt 0)

No worries MRb, I always find caffeine makes the code work ;)

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