how to select distinctively with stu_class and parent_id

Question

Working on a school notification where i have to send sms. Current problem is if a parent has two children in one class, system should just send a single message. need an sql statement to make that selection. For example in table below: parent_id 6 has three children (2 in class 2 and one in class 1). so system should only send two messages (one for the children in class 2 and one for class 1). Was trying SELECT DISTINCT

stu_id	stu_name	c_id	parent_id
1	james	2	6
2	eric	1	6
3	john	2	5
4	turin	2	6

par_id	phone_num
1	0233
2	0244
5	0245
6	0247

Does this answer your question? [MySQL select one column DISTINCT, with corresponding other columns](https://stackoverflow.com/questions/5967130/mysql-select-one-column-distinct-with-corresponding-other-columns) — Will B., Mar 15 '21 at 04:14
no please. simply put if you have two or more children in one class, single notification sent sent to parent but if not in one class, then multiple notifications sent to same parent. — log.indices, Mar 15 '21 at 04:21

score 0 · Answer 1 · answered Mar 15 '21 at 07:24

I assume the first table is table1, the second table is table2. As a result, it displays the list of sms to be sent, eliminating parent and class coincidences

select result.sms, 
        result.c_id, 
        result.parent_id, 
        result.phone_num 
        from
            (select stt.*, 
            ROW_NUMBER() over (partition by stt.c_id, stt.parent_id order by stt.c_id) as sms 
            from 
                (select table1.stu_id, 
                table1.stu_name,
                dense_rank() over(order by parent_id) as parent_id, 
                dense_rank() over(order by c_id) as c_id, 
                table2.phone_num 
                from Table1 inner join Table2 on Table1.parent_id = Table2.par_id) as STT) as result 
                where result.sms = 1

score 0 · Answer 2 · answered Mar 15 '21 at 12:48

You want a single row per parent and class. That suggests aggregation:

select t1.parent_id, t2.phone_num, t1.c_id,
       count(*) as num_in_class
from t1 join
     t2 
     on t1.parent_id = t2.par_id
group by t1.parent_id, t2.phone_num, t1.c_id;

This should give you the information you need for each message.

how to select distinctively with stu_class and parent_id

2 Answers2