CODEWITHSUNDEEP
mysqlsqlunion
Hansa
Hansa

Reputation: 149

Mysql:Avoid Nulls in Union All query

Please help me to resolve this. i am getting null values when 2nd column has resuls in same way 1st column.

select 
   (case when parents  = '3' then child end) 3_rec,
   (case when parents  = '10' then child end) 10_rec
from
(
  SELECT concat(a.name,' (',b.count,')') as child,b.parent as parents FROM wp_terms a,wp_term_taxonomy b where 
a.term_id=b.term_id and b.parent = 3 and b.taxonomy = 'category'
  union all
  SELECT concat(a.name,' (',b.count,')') as child,b.parent as parents FROM wp_terms a,wp_term_taxonomy b where 
a.term_id=b.term_id and b.parent = 10 and b.taxonomy = 'category'
) d order by 1,2 asc

Result i am expecting.Null should come last.

3_rec|10_rec
------------
row1 | row1
row2 | row2
row3 | row3
     | row4
     | row5

Upvotes: 1

Views: 214

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You have a strong misunderstanding of what union all does. Your select statement:

select (case when parents  = '3' then child end) 3_rec,
      (case when parents  = '10' then child end) 10_rec

is always going to return NULL in at least one of the columns.

You seem to want to align columns. First, I would ask if the following query is sufficient for what you want:

  SELECT concat(a.name,' (',b.count,')') as child,b.parent as parents
  FROM wp_terms a join
       wp_term_taxonomy b 
       on a.term_id=b.term_id
  WHERE b.parent in (3, 10) and b.taxonomy = 'category'

This returns the values on separate rows. Or, you could do:

  SELECT b.parent,
         group_concat(concat(a.name,' (',b.count,')'), ';') as children
  FROM wp_terms a join
       wp_term_taxonomy b 
       on a.term_id=b.term_id
  WHERE b.parent in (3, 10) and b.taxonomy = 'category'
  group by p.parent;

Aligning lists in two column is not a SQL forte (possible, but not easy). So if there is another solution, go for that.

EDIT:

To get what you want, you need a line number for the two lists. And you don't have one, so you have to create one with a variable.

select max(case when parent = 3 then child end) as "3_child",
       max(case when parent = 10 then child end) as "10_child"
from (SELECT concat(a.name,' (',b.count,')') as child, b.parent as parents,
             @rn := if(@parent = b.parent, @rn + 1, 1) as rn,
             @parent := b.parent
      FROM wp_terms a join
           wp_term_taxonomy b 
           on a.term_id=b.term_id cross join
           (select @rn := 0, @parent := '') const
      WHERE b.parent in (3, 10) and b.taxonomy = 'category'
      order by b.parent
     ) t
group by rn
order by rn;

Upvotes: 3

Related Questions