Combine multiple rows into one MySQL Join

Question

I couldn't think of a better way to ask this question, but here goes:

I have 2 tables. The first is a table of items for label printing jobs. We'll call it pj_items The columns are simply: job_id, part_num and qty

The second table is a list of finishes called PartFinishes on parts with just 2 columns of: PartNumber and FinishId

When I want to retrieve information for the label printer I use:
select pj_items.part_num, pj_items.qty, PartFinishes.FinishId from pj_items
join PartFinishes on PartFinishes.PartNumber = pj_items.part_num
where job_id = 1

This query works fine and gives me the right result when each part has only one finish. The problem is when there are 2 or more finishes on a part.

For example: The part 99401326 has 2 finishes. On my pj_items table I have one row for that part so I can print one label. When I use this query it returns 2 rows for that part each with a different FinishId Unfortunately that would result in 2 labels printed. I need both of those finishes on one row since I only want one label printed with both FinishId's present.

I had a similar query which returned the finishes as new columns (f1,f2,f3) But it would still return 2 rows in the given example with f1 defined in the first row and f1 as null in the second row and f2 being defined in the second.

How can I get that into one row?

Hopefully that makes sense, anyone have any suggestions?

If you need anymore info, let me know.

Thanks!

EDIT:

The pj_items table may have duplicate entries and I need to preserve those as separate rows. I just need to have all the finishes for each part in one row with its respective part.

Example
Here is an example of the pj_items table:

Here is the relevant information from the PartFinishes table:
PartFinishes table

And here is the result using the original query:
results

What I need in the result is the two rows of 99401326 are one with both of the finishes in that row, while maintaining the 2 seperate rows for the 99401077 since I want 2 labels of those.

piotrm · Accepted Answer · 2012-01-12 17:39:45Z

2

You can use GROUP_CONCAT to combine all finish ID's for a given part into one column.

SELECT pj_items.part_num, 
       pj_items.qty, 
       GROUP_CONCAT(PartFinishes.FinishId) as FinishIds
FROM pj_items
JOIN PartFinishes 
  ON PartFinishes.PartNumber = pj_items.part_num
WHERE job_id = 1
GROUP BY PartFinishes.PartNumber

@comment: However if you want to keep rows from pj_items separated you will need to join PartFinishes already grouped by PartNumber:

SELECT pj_items.part_num, 
   pj_items.qty, 
   FinishesGrouped.FinishIds
FROM pj_items
JOIN 
  ( SELECT PartNumber, GROUP_CONCAT(FinishId) as FinishIds
    FROM PartFinishes
    GROUP BY PartNumber ) FinishesGrouped
ON
  pj_items.part_num = FinishesGrouped.PartNumber

edited Jan 12, 2012 at 17:39

answered Jan 12, 2012 at 15:59

piotrm

12.4k5 gold badges33 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Matt Swanson Over a year ago

That works. The problem then is if I want to print multiple labels of one part, I'd have 2 or 3 rows in the pj_items table of the same part. Those rows get combined into one with the same finish multiple times in the FinishIds column. I should probably add more specifics to my question. Thanks for the answer though!

Collectives™ on Stack Overflow

Combine multiple rows into one MySQL Join

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related