how to count? - MySQL

[Merlin]

Hi there,

I am doing a count on two tables and do want to know how many rows will
be returned back. Unfortunatelly the result is instead of 3 is 11.

Here is the table structure:

table test: ID, text, title
table tags: ad_id, tag

where ad_id is refering to ID

This is my SQL:

SELECT COUNT( * ) AS c
FROM test cl
LEFT JOIN tags AS ta ON ta.ad_id = cl.ID
WHERE cl.confirmed =1
AND cl.country = 'de'
AND (

MATCH (
cl.text, cl.title
)
AGAINST (
'harlekinpudel'
IN BOOLEAN
MODE
)
OR ta.tag = 'harlekinpudel'
)

The real result should be 3 as the term "harlekinpudel" is found two
times in tags and once in test. The table tags is a helper for test that
contains tags that are associated with the entries in table test. Table
test contains the row I am searching for. I would like to know how many
rows contain either in the title or text the word, or inside one of the
many tags belonging to each row.

Has anybody an idea what I am doing wrong?

Thank you for any help,

Merlin

[Paul Lautman]

Merlin wrote:

> Hi there,
>
> I am doing a count on two tables and do want to know how many rows
> will be returned back. Unfortunatelly the result is instead of 3 is
> 11.
> Here is the table structure:
>
> table test: ID, text, title
> table tags: ad_id, tag
>
> where ad_id is refering to ID
>
> This is my SQL:
>
> SELECT COUNT( * ) AS c
> FROM test cl
> LEFT JOIN tags AS ta ON ta.ad_id = cl.ID
> WHERE cl.confirmed =1
> AND cl.country = 'de'
> AND (
>
> MATCH (
> cl.text, cl.title
> )
> AGAINST (
> 'harlekinpudel'
> IN BOOLEAN
> MODE
> )
> OR ta.tag = 'harlekinpudel'
> )
>
> The real result should be 3 as the term "harlekinpudel" is found two
> times in tags and once in test. The table tags is a helper for test
> that contains tags that are associated with the entries in table
> test. Table test contains the row I am searching for. I would like to
> know how many rows contain either in the title or text the word, or
> inside one of the many tags belonging to each row.
>
> Has anybody an idea what I am doing wrong?
>
> Thank you for any help,
>
> Merlin

Well I just tried it out and I got 3