I have this MySQL request:
SELECT *
FROM candy
RIGHT JOIN candytype ON candy.user_id = 1 AND candytype.candy_id = candy.id;
In my DB everything shows up but I see the same candy row showing twice because that one candy has two types. Is there a way that if it shows up once, that MySQL does not show it again in the result?
It just useless and I assume it makes my DB work more. I am just looking for a way to filter out...
If you want just any old type, you can do this (here I'm guessing at column names):
SELECT candy.id, candy.name, min(candytype.type)
FROM candy
RIGHT JOIN candytype
ON candy.user_id = 1
AND candytype.candy_id = candy.id GROUP BY candy.id;
If you want one row/candy, but you want to see all the types you can do:
SELECT candy.id, candy.name, GROUP_CONCAT(candytype.type)
FROM candy
RIGHT JOIN candytype
ON candy.user_id = 1
AND candytype.candy_id = candy.id GROUP BY candy.id;
Here are some solutions:
Use WHERE to filter out unwanted candy types.
SELECT *
FROM candy
RIGHT JOIN candytype ON candy.user_id = 1 AND candytype.candy_id = candy.id
WHERE candytype.type = 'lolipop';
You might use a GROUP BY clause (but be careful with that, as it groups all rows containing same values, in this case having the same user_id and candy_id):
SELECT *
FROM candy
RIGHT JOIN candytype ON candy.user_id = 1 AND candytype.candy_id = candy.id
GROUP BY candy.user_id, candytype.candy_id;
I just needed to use DISTINCT.
SELECT DISTINCT candy.id, candy.name
FROM candy
RIGHT JOIN candytype
ON candy.user_id = 1
AND candytype.candy_id = candy.id;