Professional Web Applications Themes

how to count? - MySQL

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 ...

  1. #1

    Default how to count?

    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
    Merlin Guest

  2. #2

    Default Re: how to count?

    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


    Paul Lautman Guest

Similar Threads

  1. Need Help with Count
    By TylerStonestreet in forum Macromedia ColdFusion
    Replies: 3
    Last Post: February 26th, 06:58 PM
  2. Count ()
    By Angelosalsa in forum Coldfusion Database Access
    Replies: 0
    Last Post: February 17th, 03:57 AM
  3. COUNT(*)
    By Craig Roberts in forum PHP Development
    Replies: 0
    Last Post: August 1st, 08:39 AM
  4. count
    By dennis in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 7th, 07:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139