Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    405
    Thanks
    17
    Thanked 2 Times in 2 Posts

    query duplicates

    I had a query that was pulling information from the database, but its a connector database, and it pulled numerous entries.

    This was that original:
    PHP Code:
    SELECT plantae.plant_nameplantae.taxonomic_genusplantae.scientific_nametermin_connect_to_plants.plant_id
    FROM 
    `termin_connect_to_plants`
    JOIN plantae ON termin_connect_to_plants.plant_id plantae.plant_name
    JOIN plant_locations_link ON termin_connect_to_plants
    .plant_id plant_locations_link.plant_id
    WHERE term_id
    IN 
    )
    AND 
    plant_locations_link.continents
    IN 
    ('4')
    LIMIT 0 30 
    But when I tried to limit it to only showing an item once It pulled zero results, by adding COUNT(*) AS howmany And GROUP BY plant_id HAVING howmany = 2

    PHP Code:
    SELECT plantae.plant_nameplantae.taxonomic_genusplantae.scientific_nametermin_connect_to_plants.plant_id
    COUNT(*) AS howmany 
    FROM 
    `termin_connect_to_plants
    JOIN plantae 
    ON termin_connect_to_plants
    .plant_id plantae.plant_name 
    JOIN plant_locations_link 
    ON termin_connect_to_plants
    .plant_id plant_locations_link.plant_id 
    WHERE term_id IN 
    430 
    AND 
    plant_locations_link.continents IN ('1'GROUP BY plant_id HAVING howmany 
    I'm stumped.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    What is a "connector" database?

    *******

    In any database but MySQL, that I know of, your GROUP BY is illegal, becuase it does not mention *ALL* the fields in your SELECT except the aggregate function one.

    That, is you should be using
    Code:
    GROUP BY plantae.plant_name, plantae.taxonomic_genus, 
             plantae.scientific_name, termin_connect_to_plants.plant_id
    MySQL allows you to be sloppy and omit some of the SELECTed fields, but then the results are almost always *NOT* what you would expect.

    **********

    You do realize that your HAVING will exclude any items where the count is *ANYTHING* except 2. It will exclude items with a count 1 and items with a count of 3 or more.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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