Databases
StackOverflow2013
Postsdbo
POCombine multiple rows into one MySQL Join
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCombine multiple rows into one MySQL Join
    text
    copied!<p>I couldn't think of a better way to ask this question, but here goes:</p> <p>I have 2 tables. The first is a table of items for label printing jobs. We'll call it <code>pj_items</code> The columns are simply: <code>job_id, part_num and qty</code></p> <p>The second table is a list of finishes called <code>PartFinishes</code> on parts with just 2 columns of: <code>PartNumber and FinishId</code></p> <p>When I want to retrieve information for the label printer I use: <br><code>select pj_items.part_num, pj_items.qty, PartFinishes.FinishId from pj_items</code> <br><code>join PartFinishes on PartFinishes.PartNumber = pj_items.part_num</code> <br><code>where job_id = 1</code></p> <p>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. </p> <p>For example: The part <code>99401326</code> has 2 finishes. On my <code>pj_items</code> 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 <code>FinishId</code> 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 <code>FinishId</code>'s present.</p> <p>I had a similar query which returned the finishes as new columns (<code>f1,f2,f3</code>) But it would still return 2 rows in the given example with <code>f1</code> defined in the first row and <code>f1</code> as null in the second row and <code>f2</code> being defined in the second.</p> <p>How can I get that into one row?</p> <p>Hopefully that makes sense, anyone have any suggestions?</p> <p>If you need anymore info, let me know.</p> <p>Thanks!</p> <p><strong>EDIT:</strong></p> <p>The <code>pj_items</code> 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.</p> <p><strong>Example</strong> <br>Here is an example of the pj_items table: <br><img src="https://i.stack.imgur.com/GraYv.png" alt="pj_items table"></p> <p>Here is the relevant information from the <code>PartFinishes</code> table: <br><img src="https://i.stack.imgur.com/3aD9s.png" alt="PartFinishes table"></p> <p>And here is the result using the original query: <br><img src="https://i.stack.imgur.com/jV86A.png" alt="results"></p> <p>What I need in the result is the two rows of <code>99401326</code> are one with both of the finishes in that row, while maintaining the 2 seperate rows for the <code>99401077</code> since I want 2 labels of those.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload