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

your question is not clear .. please add a proper data sample and the expected result — ScaisEdge, Feb 21 '18 at 10:26
I am trying to add it, but I can't add line breaks in comments and I can not edit my original question. — Nicolle Pereira, Feb 21 '18 at 11:16

John Woo · Answer 1 · 2018-02-21T10:32:21.883

0

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.

edited Feb 21 '18 at 10:32

answered Feb 21 '18 at 10:26

John Woo

258,903
69
498
492

Hey thanks so much for your reply! I don't quite understand where the C. and D. come from though? – Nicolle Pereira Feb 21 '18 at 10:59
They are called alias. You can set whatever you want and useful instead of typing the whole table name. – John Woo Feb 22 '18 at 00:35

score 0 · Answer 2 · answered Feb 21 '18 at 10:31

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.

This is actually a project I'm working on and we were given a database which they admit is not a great one, but regardless we can't change it unfortunately. They tell us the information that we need to gather and that is all. — Nicolle Pereira, Feb 21 '18 at 11:01
Ah. Yeah I won’t comment on that, I’m sure there is a method to the madness. Best of luck! — Zoe Edwards, Feb 21 '18 at 17:14

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

2 Answers2