Databases
StackOverflow2013
Postsdbo
POExtract linked data from one row in one table to multiple rows in another table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExtract linked data from one row in one table to multiple rows in another table
    primarykey
    data
    text
    <p>I am creating a game and have come across the need to select the name from a table (Table1) based on the ID value listed in a single row.</p> <p>Example of the row</p> <pre> RowID Unit1 Unit2 Unit3 Unit4 </pre> <p>And say that row 1 is populated with data for units as</p> <pre> wfmatch RowID Unit1 Unit2 Unit3 Unit4 ----- ----- ----- ----- ----- 1 1 2 3 4 </pre> <p>Then in my second table (<code>Table2</code>) I have the actual names</p> <pre> wfunits UnitID Name ------ ---------- 1 Firstitem 2 Seconditem 3 Thirditem 4 Fourthitem </pre> <p>In one query or as close as possible I would like to be able to obtain the names for the ID's listed from the first table giving a result like so:</p> <pre> this is the end result as achieved with code at the bottom. (+4 more names) RowID Unit1 Unit2 Unit3 Unit4 ----- --------- ---------- --------- ---------- 1 Firstitem Seconditem Thirditem Fourthitem </pre> <p>I have had a go with JOIN statements but I have so far been lacking in the knowledge on how to use them properly and I feel this is probably the way to get the result, I just can't figure it out.</p> <p>Edit: Attempted Code</p> <pre><code>SELECT * FROM wfmatch AS t1 INNER JOIN wfunits AS t2 ON t1.crunit1 = t2.id WHERE t1.mid = 1 </code></pre> <p>result: negative, only one name provided.</p> <p>Working end result:</p> <pre><code>SELECT m.mid, u1.name AS Unit1, u2.name AS Unit2, u3.name AS Unit3, u4.name AS Unit4, u5.name AS Unit5, u6.name AS Unit6, u7.name AS Unit7, u8.name AS Unit8 FROM wfmatch AS m INNER JOIN wfunits AS u1 ON m.crunit1=u1.id INNER JOIN wfunits AS u2 ON m.crunit2=u2.id INNER JOIN wfunits AS u3 ON m.crunit3=u3.id INNER JOIN wfunits AS u4 ON m.crunit4 =u4.id INNER JOIN wfunits AS u5 ON m.counit1=u5.id INNER JOIN wfunits AS u6 ON m.counit2=u6.id INNER JOIN wfunits AS u7 ON m.counit3=u7.id INNER JOIN wfunits AS u8 ON m.counit4 =u8.id WHERE mid=1 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAdsy2010
    1. USAdsy2010
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COthanks @Andriy M, couldn't figure out the formatting thing.
      singulars
      1. POExtract linked data from one row in one table to multiple rows in another table
      1. USAdsy2010
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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