2

I am trying to display some information from different tables in a database. One of these tables is called job_pieces. In this table there are fields ID, CompanyID and PieceType. Each company can have many ID and PieceTypes. I will not know how many PieceTypes each company has. This is just a small example but some companies have nearly 30 PieceTypes. Here is a sample database: http://www.sqlfiddle.com/#!9/6d528/2

SELECT  c.Name,
        COUNT(distinct jn.ID) as Jobs
        GROUP_CONCAT(DISTINCT jp.PieceType) as AllPieceTypes,
        jp.PieceType as PieceType        
FROM customer c
LEFT JOIN job_new jn ON c.JobID = jn.ID
LEFT JOIN job_pieces jp ON jn.JobID = jp.JobID
WHERE c.Company_ID = compid
GROUP BY c.ID

There result of this query is this:

enter image description here

I added the line GROUP_CONCAT so you can see all the PieceTypes the jobs have. So in this query I need to display all the jobs and PieceTypes a company has. The problem is with the column PieceType. As you can see in the image on the first row it is only displaying one type, when there should be 4. And I also want each PieceType to be a separate column, something like this:

enter image description here

Splitting the AllPieceTypes columns into separate columns for each piece seems to be a difficult thing, from the searching I have done. So am I joining the job_pieces table wrong? Why is only one type being displayed in the PieceType column when more exist? And how can I display each PieceType in its own column? They need to be in their own column because each company only has one row. So I need the row to continue when displaying PieceTypes

I found something similar here: How to parse a string and create several columns from it? But I am not sure how to edit this for my query.

Community
  • 1
  • 1
user123456789
  • 1,914
  • 7
  • 44
  • 100
  • What you want is a pivot operation. Look [here](http://buysql.com/mysql/14-how-to-automate-pivot-tables.html) and [here](http://buysql.com/mysql/12-how-to-pivot-tables.html) – SouravA Jul 10 '15 at 02:58

1 Answers1

0

There are different approaches you can take. But the real issue is that you need to pre-determine how many such types there are. SQL queries return a fixed set of rows, which is why group_concat() is convenient.

Here is an illustration of one method that returns the first three piece types:

SELECT  c.Name,
        COUNT(distinct jn.ID) as Jobs
        GROUP_CONCAT(DISTINCT jp.PieceType) as AllPieceTypes,
        substring_index(GROUP_CONCAT(DISTINCT jp.PieceType ORDER BY jp.PiectType), ',', 1) as PieceType1,
        substring_index(substring_index(GROUP_CONCAT(DISTINCT jp.PieceType ORDER BY jp.PiectType), ',', 2), ',', -1) as PieceType2,
        substring_index(substring_index(GROUP_CONCAT(DISTINCT jp.PieceType ORDER BY jp.PiectType), ',', 3), ',', -1) as PieceType3
FROM customer c LEFT JOIN
     job_new jn
     ON c.JobID = jn.ID LEFT JOIN
     job_pieces jp
     ON jn.JobID = jp.JobID
WHERE c.Company_ID = compid
GROUP BY c.ID

(If there are fewer than 3 for some jobs, then the logic is a little bit more complicated, because this will duplicate the values.)

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786