using JOIN and subquery in mysql

Question

I posted a question about 2 weeks ago about 'one to many' relation between SQL tables. Now I have a bit of a different scenario. Basically, there are two tables - coffee_users and coffee_product_registrations. The latter is connected to coffee_users table with 'uid' column. So basically coffee_users.uid = coffee_product_registrations.uid

A single user can have multiple products registered.

What I want to do is to display some product information (from coffee_product_registrations) along with some user information (from coffee_users), BUT retrieve only those rows that have more than 1 product registrations.

So to simplify, here are the steps I need to take:

1. Join two tables
2. Select users that have multiple products registered
3. Display all their products along with their names and stuff

My current SQL query looks like this:

SELECT c.uid, c.name, cpr.model 
FROM coffee_users c 
JOIN coffee_product_registrations cpr on c.uid = cpr.uid
GROUP BY c.uid 
HAVING COUNT(cpr.uid) > 1

This joins the two tables on 'uid' column but displays only 1 row for each user. It selects just users that have multiple products registered.

Now I need to take these IDs and select ALL the products from coffee_product_registrations based on them.

I cannot figure out how to put this in one query.

Answer 1

Replace cpr.*, c.* with columns which you want to extract feom the query

Try this:

SELECT cpr.*, c.*
FROM coffee_product_registrations cpr 
INNER JOIN coffee_users c ON c.uid = cpr.uid
INNER JOIN (SELECT cpr.uid 
            FROM coffee_product_registrations cpr 
            GROUP BY cpr.uid 
            HAVING COUNT(DISTINCT cpr.productId) > 1
           ) AS A ON c.uid = A.uid;