Databases
StackOverflow2013
Postsdbo
POMatching values in two tables query (SQL and ColdFusion)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMatching values in two tables query (SQL and ColdFusion)
    primarykey
    data
    text
    <p>I have a query that has a list of base values and a list of language values. Each value has a key that matches to the other. The base values are stored in one table and the language values in another. My problem is that I need to get all matching base values removed from the QUERY except for one. Then, I export that query into an Excel spreadsheet (I can do this portion fine) and allow the user to edit the language values.</p> <p>When the user edits and/or inserts new language values, I need to update the database except now writing over any matching values in the database (like those that were removed the first time).</p> <p>In simplicity, the client pays for translations and if I can generate a sheet that has fewer translations needed (like phrases that reappear often) then they can save money, hence the project to begin with. I realize the downside is that it is not a true linked list, where all matching values all belong to one row in the language table (which would have been easy). Instead, there are multiple values that are identical that need to be updated as described above.</p> <hr> <p>Yeah, I'm confused on it which is why it might seem a little vague. Here's a sample:</p> <pre><code>Table 1 Item Description1 Item Description2 Item Description3 Item Description2 Item Description2 Item Description4 Item Description5 Item Description6 Item Description3 Table 2 Item Desc in other Language1 Item Desc in other Language2 Item Desc in other Language3 (blank) Item Desc in other Language3 Item Desc in other Language4 Item Desc in other Language5 *blank* </code></pre> <p>Desired Result (when queried)</p> <p>Table 1 Item Description1 Item Description2 Item Description3 Item Description4 Item Description5 Item Description6</p> <pre><code>Table 2 Item Desc in other Language1 Item Desc in other Language2 Item Desc in other Language3 (filled by matching row in Table 2) Item Desc in other Language4 Item Desc in other Language5 Item Desc in other Language6 (blank, returned as empty string) </code></pre> <p>The user makes their modifications, including inserting data into blank rows (like row 6 for the language) then reuploads:</p> <pre><code>Table 1 Item Description1 Item Description2 Item Description3 Item Description2 Item Description2 Item Description4 Item Description5 Item Description6 Item Description3 Table 2 Item Desc in other Language1 Item Desc in other Language2 Item Desc in other Language3 (now matches row below) Item Desc in other Language3 Item Desc in other Language4 Item Desc in other Language5 Item Desc in other Language6 (new value entered by user) </code></pre> <p>There is also a resource key that matches each "Item Description" to a single "Item Desc in other Language". The only time they are ever going to see each other is during this translation process, all other times the values may be different, so the resource keys can't simply be changed to all point at one translation permanently.</p> <p>I should also add, there should be no alteration of the structure of the tables or removing rows of the table.</p> <hr> <p>Ok, here's an updated revisal of what I would LIKE the query to do, but obviously does not do since I actually need the values of the joined table:</p> <pre><code>SELECT pe.prodtree_element_name_l, rs.resource_value, pe.prodtree_element_name_l_rk FROM prodtree_element pe LEFT JOIN resource_shortstrings rs ON pe.prodtree_element_name_l_rk = rs.resource_key WHERE rs.language_id = '5' AND pe.prodtree_element_name_l &lt;&gt; '' GROUP BY pe.prodtree_element_name_l </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBill the Lizard
    1. USOrganiccat
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMatching values in two tables query (SQL and ColdFusion)
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POMatching values in two tables query (SQL and ColdFusion)
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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