Inner Join on the Same Table using Where to Filter-CodePudding

Thank you for helping me out, I'm still learning SQL. I've got a master table called JEDI that I'm trying to get specific information on using Inner JOIN and Where conditions. Specifically, I'm looking to get a count of JEDI and their rank who visited specific planet between certain dates.

And then I want to use an Inner JOIN on the same table to filter and only show me the results from JEDI who had more than 1 padawan in that specific planet. I think the second where condition is throwing me off. I've pasted my code that I've used and an example data table

My results should just be 2,Master for Obi Wan as his the only one who meets the criteria

SELECT COUNT(jedi.jedi_id),jedi.rank_id
FROM jedi
WHERE jedi.date >='2022-01-01' AND jedi.date <='2022-06-31' AND jedi.planet='Tatoine'
INNER JOIN jedi ON jedi.jedi_id WHERE COUNT(jedi.padawan)>=2
GROUP BY jedi.rank_id

CodePudding user response：

You can use having to replace this INNER JOIN jedi ON jedi.jedi_id WHERE COUNT(jedi.padawan)>=2

CodePudding user response：

First you need to group the column and use HAVING COUNT to count duplicate values.

SELECT COUNT(jedi.jedi_id),jedi.rank_id
FROM jedi
WHERE jedi.date BETWEEN '2022-01-01' AND '2022-06-31' 
                                     AND jedi.planet='Tatoine'
GROUP BY jedi.jedi_id, jedi.rank_id
HAVING COUNT(jedi.jedi_id) > 1