SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select statement problem

    If I could have some help with this select statement:

    SELECT DISTINCT count( * )
    FROM `mos_gs_grave` g, `mos_gs_gravemarker` gm, `mos_gs_person` p
    WHERE g.`graveid` = gm.`graveid` AND gm.gravemarkerid = p.gravemarkerid AND LOWER( p.`lastname` )
    LIKE 'coombes%' AND LOWER( g.`code` )
    LIKE '%w%' LIMIT 0 , 30
    In this query, one grave may contain many gravemarkers, one gravemarker may commemorate many people. In this instance the one grave has one gravemarker with two people, both with the name "Coombes". Unfortunately the query returns a total of two, when what I am after is the total number of graves - in this instance one.

    Should be easy but all my hard won SQL knowledge from several years back seems to have slowly dribbled out the back of my head when I wasn't looking.

    BTW - I'm on MySQL 4.1
    Last edited by spunkymungbeans; Feb 15, 2007 at 20:29. Reason: Clarification

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    select count(distinct g.graveid) from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Too easy! I can't believe I forgot that.

    Thanks.


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
  •