$q = "SELECT c.city_name, f.fruit_name, t.tree_name, fl.flower_name
FROM users AS u INNER JOIN
city AS c ON c.city_id = u.city_id INNER JOIN
fruits AS f ON f.fruit_id = u.fruit_id INNER JOIN
trees AS t ON t.tree_id = u.tree_id INNER JOIN
flowers AS fl ON fl.flower_id = u.flower_id
WHERE c.city_name = 'LA' ";
This query would return something like:
apples
apples
apples
kiwis
kiwis
roses
roses
oak
oak
oak
What I want is only one instance of every fruit, flower and tree:
apples
kiwis
roses
oak
I probably can loop all fruits, flowers and trees into separate arrays and then chuck out duplicate rows but can I do this with SQL only? While I figure this out perhaps some kind soul can advise me on this.
Use the DISTINCT keyword in front of the column that you want to retrieve.
$q = "SELECT DISTINCT c.city_name, DISTINCT f.fruit_name, DISTINCT t.tree_name, DISTINCT fl.flower_name
FROM users AS u INNER JOIN
city AS c ON c.city_id = u.city_id INNER JOIN
fruits AS f ON f.fruit_id = u.fruit_id INNER JOIN
trees AS t ON t.tree_id = u.tree_id INNER JOIN
flowers AS fl ON fl.flower_id = u.flower_id
WHERE c.city_name = 'LA' ";
always test queries outside of php first, that way you’ll get the actual mysql error message
in your case it’s because you cannot repeat the DISTINCT keyword – it is allowed only once, and it applies to all columns in the SELECT clause
so DISTINCT isn’t even the solution you are looking for
you need to examine the relationships between users and cities and fruits and trees and flowers
unless each user can have at most only one city, only one fruit, only one flower, and only one tree, you are going to get these “duplicates” all over the place
Hey Patche, so it seems DISTINCT can only be used once, worth a try though.
Thank Rudy
unless each user can have at most only one city, only one fruit, only one flower, and only one tree, you are going to get these “duplicates” all over the place
In my case every user can have only one city, fruit, flower and tree. What the query is returning isn’t really “duplicates”, I just need to find a way to get only one of each returned fruit, flower and tree. Perhaps a PHP array function will do the trick?
SELECT 'fruit:' || f.fruit_name AS name
FROM city AS c
INNER
JOIN users AS u
ON u.city_id = c.city_id
INNER
JOIN fruits AS f
ON f.fruit_id = u.fruit_id
WHERE c.city_name = 'LA'
UNION -- to remove dupes
SELECT 'tree:' || t.tree_name
FROM city AS c
INNER
JOIN users AS u
ON u.city_id = c.city_id
INNER
JOIN trees AS t
ON t.tree_id = u.tree_id
WHERE c.city_name = 'LA'
UNION -- to remove dupes
SELECT 'flower:' || fl.flower_name
FROM city AS c
INNER
JOIN users AS u
ON u.city_id = c.city_id
INNER
JOIN flowers AS fl
ON fl.flower_id = u.flower_id
WHERE c.city_name = 'LA'