sql - mysql if statment on same row across a Group by statment -
hello have giant join has problem.
now test made test table like
create table `table1` ( `cartarticleid` int(11) null default null ) create table `table2` ( `cartarticle_id` int(11) null default null, `testvalue` int(11) null default null )
now populated first table 1 entry , joined other one.
and other
now query made
select table1.cartarticleid, if(table2.testvalue = 1, 1, null) lol, if(table2.testvalue = 3, 1, null) lol3 table1 inner join table2 on table1.cartarticleid = table2.cartarticle_id
and produces this.
but here lies problem query has group cartarticleid , crap dous first entry , noth other 1 in 1 row like:
select table1.cartarticleid, if(table2.testvalue = 1, 1, null) lol, if(table2.testvalue = 3, 1, null) lol3 table1 inner join table2 on table1.cartarticleid = table2.cartarticle_id group cartarticleid
and there have it. lol3 empty.
how can lol , lol3 filled there to??
you have use aggregate functions, if don't want random row of each group.
select table1.cartarticleid, max(if(table2.testvalue = 1, 1, null)) lol, max(if(table2.testvalue = 3, 1, null)) lol3 table1 inner join table2 on table1.cartarticleid = table2.cartarticle_id group cartarticleid
Comments
Post a Comment