mysql - how to display all values in multiple columns in SQL query -


i trying display information different tables in database. 1 of these tables called job_pieces. in table there fields id, companyid , piecetype. each company can have many id , piecetypes. not know how many piecetypes each company has. small example companies have 30 piecetypes. here sample database: http://www.sqlfiddle.com/#!9/6d528/2

select  c.name,         count(distinct jn.id) jobs         group_concat(distinct jp.piecetype) allpiecetypes,         jp.piecetype piecetype         customer c left join job_new jn on c.jobid = jn.id left join job_pieces jp on jn.jobid = jp.jobid c.company_id = compid group c.id

there result of query this:

enter image description here

i added line group_concat can see piecetypes jobs have. in query need display jobs , piecetypes company has. problem column piecetype. can see in image on first row displaying 1 type, when there should 4. , want each piecetype separate column, this:

enter image description here

splitting allpiecetypes columns separate columns each piece seems difficult thing, searching have done. joining job_pieces table wrong? why 1 type being displayed in piecetype column when more exist? , how can display each piecetype in own column? need in own column because each company has 1 row. need row continue when displaying piecetypes

i found similar here: how parse string , create several columns it? not sure how edit query.

there different approaches can take. real issue need pre-determine how many such types there are. sql queries return fixed set of rows, why group_concat() convenient.

here illustration of 1 method returns first 3 piece types:

select  c.name,         count(distinct jn.id) jobs         group_concat(distinct jp.piecetype) allpiecetypes,         substring_index(group_concat(distinct jp.piecetype order jp.piecttype), ',', 1) piecetype1,         substring_index(substring_index(group_concat(distinct jp.piecetype order jp.piecttype), ',', 2), ',', -1) piecetype2,         substring_index(substring_index(group_concat(distinct jp.piecetype order jp.piecttype), ',', 3), ',', -1) piecetype3 customer c left join      job_new jn      on c.jobid = jn.id left join      job_pieces jp      on jn.jobid = jp.jobid c.company_id = compid group c.id

(if there fewer 3 jobs, logic little bit more complicated, because duplicate values.)


Comments

Post a Comment

Popular posts from this blog

toolbar - How to add link to user registration inside toobar in admin joomla 3 custom component -

im trying write component admin side , need add user creation. dont want duplicate existing codes add link. know how add task not simple link toolbar. thx help. if wanting button opens new user screen can try in component: $bar = jtoolbar::getinstance('toolbar'); $bar->appendbutton('link', 'users', 'new user','index.php?option=com_users&task=user.add'); you need add view.html.php file wherever you're creating toolbar. is need?
Read more

linux - disk space limitation when creating war file -

i have 1 gb disk space limitation hosting app , have used 700mb of it. however want create war file directory big (around 600mb) using command : jar -cvf root.war folderpath that's why encounter quota limitation , can't following. i'm looking way delete folder content when it's applying archive conserve disk space take note can't install zip tool in ssh shell ~ the code works on test directories, test first anyway, deletes files. it's not efficient. assumptions: 300mb free space, in destructively compress 600mb folderpath directory. none of files big -- if file in folderpath 300mb or larger, fail. notes: if ' $out ' file exists, nothing. first find pipes file list while , if ' $out ' doesn't exist in loop, create it, don't update -- if does exist, update it. each pass adds 1 more file, deletes unless file directory. after loop finishes delete empty directories too. unset u ; in=folderpath ; out=roo...
Read more