GROUP BY After SQL Double Join with WHERE condition

This is similar to a question I asked the other day (MySQL select all user ids where 'xyz' is not a value in a column of another table), but it involves another table and I'd like to apply some sort of GROUP_BY to the result.

My situation is I have 3 tables, a users table, a houses table, and a keys table.

My users table is something like:

CREATE TABLE users(
    id character varying(24) NOT NULL,
    PRIMARY KEY (id)
);

my houses table is like this:

CREATE TABLE houses(
    id character varying(24) NOT NULL,
    user_one character varying(24) NOT NULL,
    user_two character varying(24) NOT NULL,
    FOREIGN KEY (user_one) REFERENCES users(id),
    FOREIGN KEY (user_two) REFERENCES users(id),
    PRIMARY KEY (user_one, user_two)
)

My keys table is like this:

CREATE TABLE key(
    id character varying(24) NOT NULL,
    user_id character varying(24) NOT NULL,
    true_key character varying(50) NOT NULL,
    PRIMARY KEY (true_key),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

As some sample data, let's say I have the following for the users table:

+------------------------+
| id                     |
+------------------------+
| 123456                 | 
| 532614                 | 
| 549481                 | 
| 832482                 | 

this for the houses table:

+------------------------+
| id| user_one| user_two |                   
+------------------------+
| 1 | 123456  | 532614   |
| 2 | 549481  | 123456   |
| 3 | 532614  | 123456   |
| 4 | 549481  | 532614   |
+------------------------+

and this for the keys table:

+------------------------+
| id| user_id | true_key |                   
+------------------------+
| 1 | 123456  | "beans"  | 
| 2 | 832482  | "rice"   |
| 3 | 832482  | "chicken"|
| 4 | 549481  | "eggs"   |
+------------------------+

Following the advice from the other post, I created a query like this:

SELECT u.id, k.true_key
FROM users u LEFT JOIN houses h ON h.user_two = u.id
AND h.user_one = '123456' 
JOIN keys as k on u.id = k.user_id
WHERE u.id <> '123456' AND h.id IS NULL;

which gives me a result like this:

+------------------------+
|     id      | true_key |                   
+------------------------+
| 549481      | 'eggs'   |
| 832482      | 'rice'   |
| 832482      | 'chicken'|
+------------------------+

which is great, but instead of returning 3 rows, I'd somehow like to return 2 rows, something like, {id: '549481', true_key: ('eggs'), id: '832482': ('rice', 'chicken')}.

The issues lies in the fact that there are multiple different true_key rows associated with each user. I think I need to somehow apply a sort of GROUP_BY operator after doing the join, but each time I tried, I ended up with a syntax error, so any help here would be appreciated! (this honestly seems like a pretty complicated query and I'm wondering if it would have just been better to store a tuple of true_keys associated with each key row instead of doing it like this, but I think the way I'm attempting to do it now is the better option, albeit the more difficult to implement). Either way I think modifying the above statement to include a GROUP BY statement would obtain the desired result here.

Thanks

1 answer

  • answered 2021-06-23 03:13 Evan

    Update, I was able to solve this with Group_concat, here's how:

    SELECT u.id, Group_concat(k.true_key) true_keys
    FROM users u LEFT JOIN houses h ON h.user_two = u.id
    AND h.user_one = '123456' 
    JOIN keys as k on u.id = k.user_id
    WHERE u.id <> '123456' AND h.id IS NULL
    GROUP BY u.id;
    

    Hope this helps someone in the future!