CODEWITHSUNDEEP
mysql
enchance
enchance

Reputation: 30411

MySQL Many-To-Many Select

Still learning the MySQL ropes and I'm trying to find out how to do a specific selection involving many-to-many. I apologize if the table names are too generic, I was just doing some self-made exercises. I try my best to be a self-learner.

I have 3 tables one of which is a linking table. How do I write the statement which says "Show which users own both an HTC and a Samsung phone" (they own 2 phones). I'm guessing the answer is in the WHERE statement but I can't figure out how to word it.

-- Table: mark3
+---------+-----------+
| phoneid | name      |
+---------+-----------+
|       1 | HTC       |
|       2 | Nokia     |
|       3 | Samsung   |
|       4 | Motorolla |
+---------+-----------+

-- Table: mark4
+------+---------+
| uid  | phoneid |
+------+---------+
|    1 |       1 |
|    1 |       2 |
|    2 |       1 |
|    2 |       3 |
|    2 |       4 |
|    3 |       1 |
|    3 |       3 |
+------+---------+

-- Table: mark5
+------+-------+
| uid  | name  |
+------+-------+
|    1 | John  |
|    2 | Paul  |
|    3 | Peter |
+------+-------+

Upvotes: 9

Views: 7673

Answers (2)

8BallDZigns
8BallDZigns

Reputation: 11

The answer I think you are looking for is:

"SELECT DISTINCT mark5.name FROM mark5 JOIN mark4 ON (mark4.uid = mark5.uid) JOIN mark3 ON (mark3.phoneid = mark4.phoneid) WHERE mark3.name = 'HTC' && mark3.name = 'Samsung'"

I believe that will answer your questions where you are pulling the Distinct Names from mark5 and joining your other tables on those specific values where phone name is HTC or Samsung

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135739

The key is in the GROUP BY/HAVING using a COUNT of DISTINCT phone names. When the count is 2, you'll know the user has both phones.

SELECT m5.name
    FROM mark5 m5
        INNER JOIN mark4 m4
            ON m5.uid = m4.uid
        INNER JOIN mark3 m3
            ON m4.phoneid = m3.phoneid
    WHERE m3.name in ('HTC', 'Samsung')
    GROUP BY m5.name
    HAVING COUNT(DISTINCT m3.name) = 2;

Upvotes: 13

Related Questions