search

Home  >  Q&A  >  body text

Inner join to the same table using filter position

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

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

P粉278379495P粉278379495420 days ago583

reply all(2)I'll reply

  • P粉878510551

    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

    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

    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

    reply
    0
  • P粉248602298

    P粉2486022982024-02-04 14:18:54

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

    reply
    0
  • Cancelreply