Hope someone can help me with this, probably something simple that I
am missing. In short:

select ceid,case_number,notes
from tblcasenotes
group by case_number
having notes LIKE '%genesis%'

returns 90 rows, which I expect

However:


select ceid,case_number,notes
from tblcasenotes
group by case_number
having Match(notes) against('genesis')

Returns nothing. There is a fulltext index on the notes field, and if
I leave off the "group by" I do get results returned. Is "Match
against" incompatible with "group by"?

Thanks
Dewayne

On 2 Aug, 16:50, "dpin...@gmail.com" <dpin...@gmail.com> wrote:
> Hope someone can help me with this, probably something simple that I
> am missing. In short:
>
> select ceid,case_number,notes
> from tblcasenotes
> group by case_number
> having notes LIKE '%genesis%'
>
> returns 90 rows, which I expect
>
> However:
>
> select ceid,case_number,notes
> from tblcasenotes
> group by case_number
> having Match(notes) against('genesis')
>
> Returns nothing. There is a fulltext index on the notes field, and if
> I leave off the "group by" I do get results returned. Is "Match
> against" incompatible with "group by"?
>
> Thanks
> Dewayne

In both cases above you should be using WHERE and not HAVING

On Aug 2, 9:07 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 2 Aug, 16:50, "dpin...@gmail.com" <dpin...@gmail.com> wrote:
>
>
>
> > Hope someone can help me with this, probably something simple that I
> > am missing. In short:
>
> > select ceid,case_number,notes
> > from tblcasenotes
> > group by case_number
> > having notes LIKE '%genesis%'
>
> > returns 90 rows, which I expect
>
> > However:
>
> > select ceid,case_number,notes
> > from tblcasenotes
> > group by case_number
> > having Match(notes) against('genesis')
>
> > Returns nothing. There is a fulltext index on the notes field, and if
> > I leave off the "group by" I do get results returned. Is "Match
> > against" incompatible with "group by"?
>
> > Thanks
> > Dewayne
>
> In both cases above you should be using WHERE and not HAVING

With a "group by" clause?

On Aug 2, 2:13 pm, ThanksButNo <no.no.tha...@gmail.com> wrote:
> On Aug 2, 9:07 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 2 Aug, 16:50, "dpin...@gmail.com" <dpin...@gmail.com> wrote:
>
> > > Hope someone can help me with this, probably something simple that I
> > > am missing. In short:
>
> > > select ceid,case_number,notes
> > > from tblcasenotes
> > > group by case_number
> > > having notes LIKE '%genesis%'
>
> > > returns 90 rows, which I expect
>
> > > However:
>
> > > select ceid,case_number,notes
> > > from tblcasenotes
> > > group by case_number
> > > having Match(notes) against('genesis')
>
> > > Returns nothing. There is a fulltext index on the notes field, and if
> > > I leave off the "group by" I do get results returned. Is "Match
> > > against" incompatible with "group by"?
>
> > > Thanks
> > > Dewayne
>
> > In both cases above you should be using WHERE and not HAVING
>
> With a "group by" clause?- Hide quoted text -
>
> - Show quoted text -

Not sure why, but I got this to work:

select ceid, case_number, notes
from tblcasenotes
where Match(notes) Against('genesis')
group by case_number

On Thu, 02 Aug 2007 20:13:59 +0200, ThanksButNo <no.no.thanks@gmail.com>
wrote:

> On Aug 2, 9:07 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>> On 2 Aug, 16:50, "dpin...@gmail.com" <dpin...@gmail.com> wrote:
>>
>>
>>
>> > Hope someone can help me with this, probably something simple that I
>> > am missing. In short:
>>
>> > select ceid,case_number,notes
>> > from tblcasenotes
>> > group by case_number
>> > having notes LIKE '%genesis%'
>>
>> > returns 90 rows, which I expect
>>
>> > However:
>>
>> > select ceid,case_number,notes
>> > from tblcasenotes
>> > group by case_number
>> > having Match(notes) against('genesis')
>>
>> > Returns nothing. There is a fulltext index on the notes field, and if
>> > I leave off the "group by" I do get results returned. Is "Match
>> > against" incompatible with "group by"?
>>
>> In both cases above you should be using WHERE and not HAVING
>
> With a "group by" clause?

Yes, offcourse. Saves grouping endless amount of rows which don't match....

The HAVING clause is more of a filter you can only run over the result
_after_ it's finished (like for instance a count of specific id's grouped
by another field), if there are requirements that can be checked before by
all means, give the server a break and use a WHERE clause.
--
Rik Wasmus

On 2 Aug, 19:13, ThanksButNo <no.no.tha...@gmail.com> wrote:
| | In both cases above you should be using WHERE and not HAVING

| With a "group by" clause

Yes of course with a GROUP BY clause.

HAVING works on the results of a query (that is why you can use a
column alias in a HAVING clause).

HAVING and WHERE are not mutually exclusive.

I'm surprised that MATCH...AGAINST in a HAVING clause doesnt' raise a
syntax error as I can't see how it would ever manage to work.

On Aug 3, 1:22 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 2 Aug, 19:13, ThanksButNo <no.no.tha...@gmail.com> wrote:
> | | In both cases above you should be using WHERE and not HAVING
>
> | With a "group by" clause
>
> Yes of course with a GROUP BY clause.
>
> HAVING works on the results of a query (that is why you can use a
> column alias in a HAVING clause).
>
> HAVING and WHERE are not mutually exclusive.
>
> I'm surprised that MATCH...AGAINST in a HAVING clause doesnt' raise a
> syntax error as I can't see how it would ever manage to work.

I wouldn't have mentioned, except I'd read somewhere (in Sybase
documentation) that a "where" with a "group by" was an *extension* to
standard SQL syntax. I suppose if so, then MySql must have the same
extension.

ThanksButNo wrote:
> On Aug 3, 1:22 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>> On 2 Aug, 19:13, ThanksButNo <no.no.tha...@gmail.com> wrote:
>>>> In both cases above you should be using WHERE and not HAVING
>>
>>> With a "group by" clause
>>
>> Yes of course with a GROUP BY clause.
>>
>> HAVING works on the results of a query (that is why you can use a
>> column alias in a HAVING clause).
>>
>> HAVING and WHERE are not mutually exclusive.
>>
>> I'm surprised that MATCH...AGAINST in a HAVING clause doesnt' raise a
>> syntax error as I can't see how it would ever manage to work.
>
> I wouldn't have mentioned, except I'd read somewhere (in Sybase
> documentation) that a "where" with a "group by" was an *extension* to
> standard SQL syntax. I suppose if so, then MySql must have the same
> extension.

See:
http://blog.sqlauthority.com/2007/07...-where-clause/