Mysql – 3 different queries from 2 tables

MySQLrelations

I have three tables (palettes, boxes, layout) with sample values:

Palettes

|ID_P|sign|
|   1|  P1|
|   2|  P2|
|   3|  P3|
|   4|  P4|

Boxes

|ID_B|sign|
|   1|  B1|
|   2|  B2|
|   3|  B3|
|   4|  B4|

Layout

|ID_L|ID_P|ID_B|
|   1|   1|   1|
|   2|   2|   2|
|   3|   2|   3|
|   4|   3|   4|
|   5|   4|   4|

So I can have 3 cases:

One palete with one box (ID_L = 1)
One palette with more than one boxes on it (ID_L = 2,3)
One big box on more than one palette (ID_L= 4,5)

Now, I need to group it, so I need three separate queries, that will return:

for case 1: all ID_P's of 1:1 "relation"

for case 2: all ID_P's of 1:N "relation"

for case 3: all ID_P's pf N:1 "relation"

Basically I have sollutions for case 2 and 3, but have no idea how to get case 1.

Best Answer

Your first case can be done just by literaaly translating your request: find all ID_P that appear once in the layout table, and that are associated with one ID_B (i.e. one box) in the same table:

SELECT
    layout.ID_P, GROUP_CONCAT(layout.ID_L) AS layout_list
FROM
    layout
WHERE
    (SELECT count(*) FROM layout l2 WHERE l2.ID_P = layout.ID_P) = 1
    AND
    (SELECT count(*) FROM layout l3 WHERE l3.ID_B = layout.ID_B) = 1
GROUP BY
    ID_P ;

ID_P | layout_list
---: | :----------
   1 | 1

This can be further optimized to avoid having perform two subqueries. Given the fact that we have a GROUP BY, the first subquery is converted into a HAVING count(layout.ID_P) = 1. The second condition needs a JOIN to find all associated layouts to ID_B, and count those. This is done by means of:

-- Alternative
SELECT
    layout.ID_P, GROUP_CONCAT(layout.ID_L) AS layout_list
FROM
    layout
    JOIN layout l3 ON l3.ID_B = layout.ID_B
GROUP BY
    layout.ID_P 
HAVING
        count(layout.ID_P) = 1
    AND count(l3.ID_B) = 1;

You can see all the data, together with all the execution plans at dbfiddle here

Related Solutions

Mysql – Redesigning a multi-instance MySQL server for performance and resiliency

I would recommend that you set up separate clusters for different database instances. You should be able to set up multiple clusters sharing some or all of the hosts, just make sure each cluster has its own ports defined.

Retrieve Multiple Values from Different Tables in MySQL

Ok, first off I think you are almost there. From looking at the schema and data one thing I have noticed is that you seem to have overlooked the fact that a friendship is bi-directional. So when you create a friend entry from a request you also need to create one in the other direction as well:

INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`) 
VALUES (1, 2, 1, 0, '2012-08-13 18:16:11', 0, 1);

After you have done that your query should be more like the result you are after. Running this query:

select distinct u.id as `user_id`, u.username, f.id as `friend_id`, f.username as friend, s.*
from default_users as u
left join default_friend as df on df.user_id = u.id
left join default_users as f on f.id = df.friend_id
left join default_status as s on s.user_id = u.id
left join default_comment as c on c.status_id = s.status_id
order by s.status_id;

returns the following result set:

user_id username    friend_id   friend  status_id   message created_at  privacy user_id is_reply    device  
1   admin   2   demo    1   dasdasdasdasdasd    2012-08-13 19:45:37 NULL    1   0   
2   demo    1   admin   2   dasdasdasdasdasd    2012-08-13 19:46:03 NULL    2   0   
1   admin   2   demo    3   dasdsad344hbvnbnhjhgjhjghjhj    2012-08-13 21:54:53 NULL    1   0

Is this anywhere near what you are looking for?

Best Answer

Related Solutions

Mysql – Redesigning a multi-instance MySQL server for performance and resiliency

Retrieve Multiple Values from Different Tables in MySQL

Related Question