How do I join all rows with the same name in one table using MYSQL?

Question

Suppose I have a database called clubmembership that has a column for names, a column for clubs, and a column for the role they play in that club. The name Margrit would be in the column name many times, or as many times as she is in a club. If I want to see which people are members of the sewing club my query might look something like this:

SELECT DISTINCT NAME FROM CLUBMEMBERSHIP
WHERE CLUB=’SEWING’
AND ROLE=’MEMBER’;

My problem is that I can't figure out a query for who is not in the sewing club. Of course the simple 'not in' clause isn't working because there are plenty of rows which sewing does not appear in. In this database if someone is not in the sewing club, sewing does not appear under club so I imagine there is a way to join the different rows with the same name under 'name' and then potentially use the 'not in' clause

I hope this was a good explanation of this question. I have been struggling with this problem for a while now.

Thanks for your help! Nicolle

Answer 1

If I understand you correctly, you wanted to list all names that is not a member of SEWING. The Inner query will get all Names that are member of SEWING, however, the NOT EXISTS operator will get all Names that are not found in the inner query.

SELECT  DISTINCT C.NAME
FROM    CLUBMEMBERSHIP C
WHERE   C.ROLE = 'MEMBER'
        NOT EXISTS
        (
            SELECT NULL 
            FROM CLUBMEMBERSHIP D
            WHERE D.CLUB='SEWING' 
                    AND D.ROLE='MEMBER'
                    AND C.NAME = D.NAME
        )

Here's a Demo.

Answer 2

This is not something that can be solved by just changing the existing code, it is to do with the database design.

Database normalisation is the process of sorting out your database into sensible tables.

If you’re adding a person many times, then you should create a table called members instead. And if there is a list of clubs, then you should create a clubs table.

Then, you can create a table to join them together.

Here’s your three tables:

members
-------
id (int)
name (varchar)

clubs
-------
id (int)
name (varchar)

memberships
-------
member_id (int)
club_id (int)

Then you can use joins in MySQL to return the information you need.

Stack Overflow doesn’t like external links as the answer should be here, but this is a huge topic that won’t fit in a single reply, so I would briefly read about database normalization, and then read about ‘joining’ tables.