Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

I can't see the wood for the trees.

9:36 pm on Feb 15, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member

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:


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

Whereas I would have expected this:-

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.

9:52 pm on Feb 15, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

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]

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 :)
10:17 pm on Feb 15, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member

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,
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!
9:15 am on Feb 16, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member

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.

8:57 pm on Feb 16, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member

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.

10:25 pm on Feb 16, 2011 (gmt 0)

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

Featured Threads

Hot Threads This Week

Hot Threads This Month