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.

enter image description here

and other

enter image description here

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.

enter image description here

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

Popular posts from this blog

c# - How to get the current UAC mode -

postgresql - Lazarus + Postgres: incomplete startup packet -

javascript - Ajax jqXHR.status==0 fix error -