Can I GROUP BY in MySQL, but have it ignore null values when grouping, dropping a result row if necessary? -

Can I GROUP BY in MySQL, but have it ignore null values when grouping, dropping a result row if necessary? -

my tables:

t1

col_a col_b 1 100 1 200 1 300 2 400

t2

col_a col_b 100 5 100 6

t3

col_a col_b 5 100 6 200 6 300

if run query , left bring together 3 tables in order get:

1 100 5 100 1 100 6 200 1 100 6 300 1 200 null null 1 300 null null 2 400 null null

if add together grouping t1.col_a, t2.col_b:

1 100 5 100 1 100 6 (200 or 300) 1 (200 or 300) null null 2 400 null null

but don't want 3rd row show because not have value in t2.col_b. add together status column not null, remove lastly row needs stay.

in perfect query see:

1 100 5 100 1 100 6 (200 or 300) 2 400 null null

try one. t1.col_b little tricky, because if multiple t1.col_a values exists none reference t2, query select random this.

select sub.col_a, ifnull(sub.relation,t1.col_b), t2.col_b, t3.col_b from( select t1.col_a, group_concat(distinct t2.col_a) relation table1 t1 left bring together table2 t2 on t2.col_a = t1.col_b grouping t1.col_a ) sub left bring together table1 t1 on t1.col_a = sub.col_a , (t1.col_b in (sub.relation) or sub.relation null) left bring together table2 t2 on t2.col_a = t1.col_b left bring together table3 t3 on t3.col_a = t2.col_b grouping t1.col_a, t2.col_b

mysql group-by left-join