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 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