Inner join to the same table using filter position

Question

Thank you for helping me, I'm still learning SQL. I have a main table called JEDI and I'm trying to get specific information on using Inner JOIN and Where conditions. Specifically, I want to get the number of JEDIs that visited a specific planet between specific dates and their level.

I then want to use an Inner JOIN on the same table to filter and show me only the results for JEDIs that have more than 1 padawan on that particular planet. I think the second condition is throwing me off. I've pasted the code I used and the sample data table

My result should only be 2, Master Obi-Wan is 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

Answer 1

First, you need to group the columns 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

Note: This feature only works if each jedi_id has a unique value of jedi.padawan. This query is suitable for these scenarios.

However, you can improve the query by adding another condition that computes the unique jedi.padawan so that jedi.padawan< 上重复出现 is repeated on each jedi.jedi_id< Value/code> will not be included

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 AND
       COUNT (DISTINCT jedi.padawan) > 1

Answer 2

You must replace this with INNER JOIN jedi ON jedi.jedi_id WHERE COUNT(jedi.padawan)>=2