CODEWITHSUNDEEP
mysqlsqlgroup-byfull-outer-join
Sylver
Sylver

Reputation: 8967

Group rows based on multiple columns in mysql

The easiest way to explain my problem is probably to show the tables and the results I am trying to get. My latest attempted query is at the bottom (doesn't work)

I have the following tables (removed a bunch of columns for simplicity):

Client table:

-------------------------
| client_id   | name    |
-------------------------
| client_1    | Bob     |
-------------------------
| client_2    | Jane    |
-------------------------
| client_3    | Amy     |
-------------------------

Contract table:

---------------------------------------------
| contract_id | client_id | amount  | date  |
---------------------------------------------
| contract_1 | client_1   | $5      | 01/02 |
---------------------------------------------
| contract_2 | client_1   | $25     | 21/03 |
---------------------------------------------
| contract_3 | client_2   | $40     | 25/05 |
---------------------------------------------

Session table:

------------------------------------------
| session_id  | client_id | contract_id  |
------------------------------------------
| session_1   | client_1  | contract_1   |
------------------------------------------
| session_2   | client_2  | contract_3   |
------------------------------------------
| session_3   | client_2  | contract_3   |
------------------------------------------
| session_4   | client_1  | contract_2   |
------------------------------------------
| session_5   | client_1  | contract_1   |
------------------------------------------

I need to join the tables and group ALL the rows by client_id and contract_id, like this:

client 1, Bob
     |___ contract_1, $5, 01/02
            |___ Session 1
            |___ Session 5
     |___ contract_2, $25, 21/03
            |___ Session 4
client 2, Jane
     |___ contract_3, $40, 25/05
            |___ Session 2
            |___ Session 3
client 3, Amy

My latest attempt was to do a full outer join on the tables and group the result by client_id and contract_id. 

SELECT * FROM    
    (SELECT
        *
    FROM
        contracts
    LEFT OUTER JOIN 
        clients
    ON 
        clients.client_id = contracts.client_id       
    UNION 
    SELECT
        *
    FROM
        contracts
    LEFT OUTER JOIN 
        sessions
    ON 
        sessions.client_id = contracts.client_id)
AS tmp_table GROUP BY client_id, contract_id

The full outer join workaround fails because the number of columns in the various tables is different (and I need all the columns, so I am out of luck there) and it looks like "GROUP BY" would return only 1 row of each (which also doesn't work for me as I need all the rows.

Performance is no issue at all in this case.

SQL fiddle based on Ronald's answer

It's looks like it does what I need. I'll adapt it to my production tables and see if there is something I overlooked before I approve the answer

Upvotes: 1

Views: 94

Answers (1)

Ronald
Ronald

Reputation: 2882

Would the following query give the desired result?

select cl.client_id, cl.name, co.contract_id, co.amount, co.date, se.session_id
from (client cl left join contract co
     on cl.client_id = co.client_id) left join session se
                 on co.contract_id = se.contract_id
order by cl.client_id, co.contract_id, se.session_id

(as already pointed out in one of the comments: the result of a select statement is always a table)

And a small question: why is the client_id part of the session table? it is overdetermined, which can cause ambiguities within your data base.

Upvotes: 1

Related Questions