Gossamer Forum
Home : Products : DBMan SQL : Discussion :

SELECT DISTINCT

Quote Reply
SELECT DISTINCT
How can I get my Search/Modify Search/Delete Search and List All to do a SELECT DISTINCT ????

I don't want the search to return a hit/result for records that have a common field.

Can I just add something to the search string (if so, what?) or does it require a plugin?

Thanks

Simon.
Quote Reply
Re: [jai] SELECT DISTINCT In reply to
I think I can modify the LetterSearch plugin to do a DISTINCT search but I'm not sure how to modify the following line -

my $sth = $table->select ( GT::SQL::Condition->new ($column, 'LIKE', "$letter%") );

How can I change the red part to do a DISTINCT search for $letter in $column.

I've tried all sorts of things like ('DISTINCT', $column, "$letter%"), etc.. but nothing seems to work.

Any ideas?

Thanks

Simon.
Quote Reply
Re: [jai] SELECT DISTINCT In reply to
Try:

Code:
my $sth = $table->select ( GT::SQL::Condition->new ("DISTINCT($column)", 'LIKE', "$letter%") );
Quote Reply
Re: [Paul] SELECT DISTINCT In reply to
Paul,

Thanks for your reply but it didn't make any difference.

I just can't get it to work with the GT::SQL::Condition->new.

I can get it to work using -

my $sth = $table->select ('DISTINCT $column','LIKE',"$letter%");

but not with -

my $sth = $table->select ( GT::SQL::Condition->new ('DISTINCT $column','LIKE',"$letter%") );

I guess I'll just go with the one that works but I would still like to know why the other doesn't work if anyone has any ideas.

Thanks

Simon.
Quote Reply
Re: [jai] SELECT DISTINCT In reply to
You can't use:

'DISTINCT $column'

Because you are using single quotes so $column won't be interpolated.

If you notice my version above I use " "
Quote Reply
Re: [Paul] SELECT DISTINCT In reply to
Sorry, I just wrote that as an example. What I actually did was write the fieldname (no $column) but it still doesn't work.

my $sth = $table->select ( GT::SQL::Condition->new ('DISTINCT fieldname','LIKE',"$letter%") );

Anyway, just to simplify my question -

Why does this work -

my $sth = $table->select ('DISTINCT fieldname');

but this doesn't -

my $sth = $table->select ( GT::SQL::Condition->new ('DISTINCT fieldname') );

Thanks

Simon.
Quote Reply
Re: [jai] SELECT DISTINCT In reply to
I've come to the conclusion that the DISTINCT doesn't work with GT::SQL::Condition because it is not part of the WHERE clause. It needs to be used with the regular select clause as in -

my $sth = $table->select ('DISTINCT fieldname');

Is this correct??

Thanks

Simon.

To see DISTINCT incorporated with a seperate GT::SQL::Condition (WHERE clause) take a look at http://gossamer-threads.com/...orum.cgi?post=232703

Last edited by:

jai: Feb 14, 2003, 5:47 AM
Quote Reply
Re: [jai] SELECT DISTINCT In reply to
>>
Is this correct??
<<

Yes. DISTINCT doesn't work in WHERE clauses.