...

View Full Version : query duplicates



turpentyne
01-17-2012, 04:15 AM
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:

SELECT plantae.plant_name, plantae.taxonomic_genus, plantae.scientific_name, termin_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 ( 4 )
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



SELECT plantae.plant_name, plantae.taxonomic_genus, plantae.scientific_name, termin_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 = 2


I'm stumped.

Old Pedant
01-17-2012, 08:29 AM
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

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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum