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]

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

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

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!

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

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

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