CODEWITHSUNDEEP
phpmysql
Mark Alan
Mark Alan

Reputation: 455

Iget data from 1 table and check the data does not exist in second table

I have 3 tables created forum groups and group_members I want to get groups only those which are not in group members along with user id currently it is getting group which group id and user id is not present in the group members table if data is not in the table only if 1 group member exist it pulls up the record . In simple words I want to show show groups which user have not joined here is my table schema for both 3 tables

Groups
+----+----------+
| id | name     |
+----+----------+
| 1  | group 1  |
| 2  | group 2  |
| 3  | group 3  |
| 4  | group 4  |
+----+----------+

forums
+------------------+-------------+
| id | title       | group_id    |
+------------------+-------------+
| 1  | test 1      |           2 |
| 2  | test 2      |           3 |
| 3  | test 3      |           2 |
| 4  | test 4      |           3 |
| 5  | test 5      |           2 |
| 6  | test 6      |           4 |
+------------------+-------------+

Group_members
+-----------------+-------------+
| id | user_id  |  group_id    |
+-----------------+-------------+
| 1  | 107       |     2        |
| 2  | 106       |     3        |
+-----------------+-------------+

Here is my sql I have written

<?php
$sql_grp_chk = $this->db->query("SELECT * FROM groups WHERE NOT EXISTS (SELECT * FROM group_members WHERE groups.id == group_members.group_id)");
foreach($sql_grp_chk->result() as $data_ct):
    $sql_gr_coun = $this->db->query("SELECT groups.*, (SELECT count(group_id) FROM forums WHERE groups.id = forums.group_id) as forumcount FROM groups WHERE groups.id != '".$data_ct->id."' ORDER BY forumcount DESC LIMIT 5");
    foreach($sql_gr_coun->result() as $data_count):
        $sql_follow = $this->db->get_where('group_members', array('group_id' => $data_count->id));
        var_dump($data_count);
?>

<?php endforeach; ?>
<?php endforeach; ?>

Upvotes: 0

Views: 28

Answers (1)

Alex Blex
Alex Blex

Reputation: 37048

Not sure why forums is there, but to select all groups that are not linked to a user you can do left join:

select g.* from groups g
left join group_members m on m.group_id = g.id and m.user_id = :userId
where m.id is null;

EDIT:

Select top 5 groups, by number of forums linked:

select g.*, count(nullif(f.id, 1)) as cnt from groups g
inner join forums f on f.group_id = g.id
group by g.id
order by cnt desc
limit 5;

Both queries together - top 5 groups, by number of forums linked, which user has not joined yet:

select g.*, count(nullif(f.id, 1)) as cnt from groups g
left join group_members m on m.group_id = g.id and m.user_id = :userId
left join forums f on f.group_id = g.id
where m.id is null
group by g.id
order by cnt desc
limit 5;

Upvotes: 1

Related Questions