MySQL - Select all rows which not exist or a field equal to value

Question

I have two tables:

g1 :

id |  name  | 
1  |  G1    |
2  |  G2    |
3  |  G3    |
4  |  G4    | 
5  |  G5    |

g_u_groups :

id | group_id | action |
1  |    2     |  view  |
2  |    3     |  add   |
3  |    2     |  view  |
4  |    3     |  view  |
5  |    1     |  add   |

I have a problem with the following query:

SELECT * 
FROM g1 
LEFT JOIN g_u_groups ON
    g_u_groups.group_id = g1.id 
    AND (g_u_groups.action = 'add' OR g_u_groups.id is null) 
ORDER BY g_u_groups.id DESC 
LIMIT 10

Now it selects only the groups which have rows in g_u_groups.

I want to order all groups by latest 'add' action but I also want to select the groups which don't have actions.

The expected results :

id |  name  | 
1  |  G1    |
3  |  G3    |
2  |  G2    |
4  |  G4    | 
5  |  G5    |

Elaborate your question with sample data and expected result. — Viki888, Nov 22 '16 at 15:02
Kindly provide the sample data for your scenario. You have said as **I also want to select the groups which don't have actions**. I cannot see this scenario in your sample. Also provide the **expected result**. — Viki888, Nov 22 '16 at 15:17
@AhmedDousa On seeing your expected result, I will suggest `SELECT * FROM g1`. Will this solve your problem? If not kindly provide proper expected data. — Viki888, Nov 22 '16 at 15:24
@Viki888 i want to order the results by the latest actions in another table please check the expected results above — AHMED.D, Nov 22 '16 at 15:25

score 0 · Answer 1 · edited May 23 '17 at 12:30

0

Does this return the expected?

    SELECT g1.id, g1.name
    FROM g1 
    LEFT JOIN g_u_groups 
     ON g_u_groups.group_id = g1.id 
      AND g_u_groups.action = 'add' or g_u_groups.id is null 
    ORDER BY g_u_groups.id DESC, g1.id DESC
    LIMIT 10

this POST talk about sorting multiple columns, it could help you

edited May 23 '17 at 12:30

Community

1
1

answered Nov 22 '16 at 15:36

Shapi

5,493
4
28
39

same issue, but if i removed the where condition i will receive all the results my only problem will be the order of the groups. – AHMED.D Nov 22 '16 at 16:11
1

edited my post, check the link for multiple column sorting in my post – Shapi Nov 22 '16 at 16:16

score 0 · Answer 2 · answered Nov 23 '16 at 17:28

Here is the SQL returning by latest add action also showing items without action:

SELECT * 
FROM g1 
LEFT JOIN g_u_groups ON
g_u_groups.group_id = g1.id 
where (g_u_groups.action = 'add' OR g_u_groups.id is null) 
ORDER BY g_u_groups.id DESC 
LIMIT 10;

MySQL - Select all rows which not exist or a field equal to value

2 Answers2