Count the number of times a data is repeated in the table sql Query-CodePudding

I have a database that has many tables, and one that only has relationships, from that table I need to obtain the number of times a data is repeated in the table

I want to make a query that returns the id of the person who has exactly 2 homes

SELECT
    person.name AS 'name',
    person.id AS 'id of person',
    home.location AS 'location of home'
FROM
    person JOIN intermediary
    ON
    person.id = intermediary.person
WHERE
    COUNT (
        SELECT
            person.id_person
        FROM
            intermediary JOIN person
            ON
            intermediary.person = person.id
    ) = 2

This is what I thought, that in the where it is counted how many times each person appears and if it appears 2 times that it returns only that.

Database diagram

CodePudding user response：

So the basic idea is the following:

SELECT (A BUNCH OF FIELDS)
FROM (GIVE ME THE JOINED TABLES (PERSON   INTERMEDIARY   HOME))
WHERE
    Person.id = (
        (GIVE ME THE ID OF THE PERSON WHOS ID IS APPEARING EXACTLY 2x INSIDE INTERMEDIARY)
    )

Joining the tables is easy enough with two consecutive JOINs:

(Person JOIN Intermediary ON Person.id = person) JOIN Home ON Intermediary.home = Home.id

For the id of the person who is appearing twice in the intermediary table, we just count how often each person appears in the table by using the GROUP BY operator:

SELECT person, COUNT(person) as 'count' FROM Intermediary GROUP BY person

...from which we select only the rows where count = 2, by wrapping the just mentioned query (Q) like so:

SELECT person FROM ( Q ) AS personHomeCount WHERE personHomeCount.count = 2 LIMIT 1

It is assumed that a combination of a person and a home is unique and can only appear once in the table. Additionally, note the LIMIT 1 as we can only accept one value returned by the query. If multiple people have exactly two homes, the first person will be the selected one.

Finally, stiching these queries together, we get:

SELECT
    Person.name,
    Person.id as 'id of person',
    Home.location as 'location of home'
FROM
    (
        (
            Person
            JOIN Intermediary ON Person.id = person
        )
        JOIN Home ON Intermediary.home = Home.id
    )
WHERE
    Person.id = (
        SELECT
            person
        FROM
            (
                SELECT
                    person,
                    COUNT(person) as 'count'
                FROM
                    Intermediary
                GROUP BY
                    person
            ) AS personHomeCount
        WHERE
            personHomeCount.count = 2 LIMIT 1
    )

Here is the SQL-Fiddle for further demonstration: https://www.db-fiddle.com/f/nJrmwtQfASzTPp851Q9SSf/0

Hope I could help!