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
1 2 3 4 5 |
|
P粉8785105512024-02-04 16:59:14
First, you need to group the columns and use HAVING COUNT
to count duplicate values.
1 2 3 4 5 6 |
|
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
1
2
3
4
5
6
7
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
reply0

P粉2486022982024-02-04 14:18:54
You must replace this with INNER JOIN jedi ON jedi.jedi_id WHERE COUNT(jedi.padawan)>=2
reply0
Cancel