CODEWITHSUNDEEP
mysqlsql
Omar
Omar

Reputation: 383

Inner Join on the Same Table using Where to Filter

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

enter image description here

Upvotes: 0

Views: 445

Answers (2)

Its_Me
Its_Me

Reputation: 57

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

Note: This will only work if every jedi_id has a unique value of jedi.padawan. This query would work in those scenario.

However you can improve the query by adding another condition that counts unique jedi.padawan so that repeated values of jedi.padawan on every 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

Upvotes: 1

learning
learning

Reputation: 610

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

Upvotes: 2

Related Questions