Need SQL to select rows that haven't been translated

T

test

Board Regular
Joined
Apr 17, 2002
Messages
182
Hi there,

I'm looking for a sql query that will select rows in my db that haven't been translated. I have an application with 2 languages (English and French). I'm looking to find the one's not translated.

I've got 3 fields in the table that identify the language.

object_id and field_name and language_code

language_code =E is english
language_code = F is french

Can anyone help?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
How do you determine if something has been translated?

Can't you just do a query on the language_code?
 
Upvote 0
Exactly, that's my question I need to know what has and what hasn't been translated.
 
Upvote 0
I don't think I understand fully - how are you defining whether something has been translated or not?

Is something translated if language_code has a specific value?
 
Upvote 0
Initially everything was done in english. Therefore language_code='E', but then we started adding french translations. When we did this we added language_code='F', but not all of the translations were added.
I'm looking for the ones that haven't been added.

So my table looks something like this (excuse my french)

object ID, FieldName, Language code
1 , fieldname1, E
1, nom du field, F
2, fieldname2, E
3, fieldname 3, E
3, fieldname 3, F
3, another fieldname 3, E
4, outra nom du field 3, E

In this case I want the results of my sql to get:
select object_id, field_name, languge_code from table where language_code='F' and ... (please fill in the rest)

2, fieldname2, E
 
Upvote 0
I still don't understand fully.

Surely you just want to return the records where the language code is E.

If so just change the F in the query you already have to E.

SELECT object_id, field_name, languge_code
FROM table
WHERE language_code='E';

Or am I totally missing the point?
 
Upvote 0
Hi.

You are missing the point.
If I select everything='F' that will give me all the ones that have already been translated.

I want the ones that have NOT been translated.

If I select the ones with language_code='E' that will give me all the records. But I want just the ones that have NOT been translated.
 
Upvote 0
Is this what you mean?

<html xmlns:eek:="urn:schemas-microsoft-com:eek:ffice:eek:ffice"xmlns:x="urn:schemas-microsoft-com:eek:ffice:excel"xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=windows-1252"><meta name=ProgId content=Excel.Sheet><meta name=Generator content="Microsoft Excel 9"><link rel=File-List href="./Language_files/filelist.xml"><link rel=Edit-Time-Data href="./Language_files/editdata.mso"><link rel=OLE-Object-Data href="./Language_files/oledata.mso"><style></style></head><body link=blue vlink=purple><table x:str border=0 cellpadding=0 cellspacing=0 width=624 style='border-collapse: collapse;table-layout:fixed;width:468pt'> <col class=xl31 width=47 style='mso-width-source:userset;mso-width-alt:2005; width:35pt'> <col width=107 style='mso-width-source:userset;mso-width-alt:4565;width:80pt'> <col class=xl31 width=81 style='mso-width-source:userset;mso-width-alt:3456; width:61pt'> <col width=56 style='width:42pt'> <col width=333 style='mso-width-source:userset;mso-width-alt:14208;width:250pt'> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 width=47 style='height:11.25pt;width:35pt'></td> <td width=107 style='width:80pt'></td> <td class=xl31 width=81 style='width:61pt'></td> <td width=56 style='width:42pt'></td> <td width=333 style='width:250pt'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'></td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'>FieldTable</td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'></td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl29 style='height:11.25pt'>ObjectID</td> <td class=xl29 style='border-left:none'>FieldName</td> <td class=xl29 style='border-left:none'>LanguageCode</td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl32 width=47 style='height:11.25pt;width:35pt' x:num>1</td> <td class=xl30 width=107 style='border-left:none;width:80pt'><span style="mso-spacerun: yes"> </span>fieldname1</td> <td class=xl32 width=81 style='border-left:none;width:61pt'>E</td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl32 width=47 style='height:11.25pt;border-top:none; width:35pt' x:num>1</td> <td class=xl30 width=107 style='border-top:none;border-left:none;width:80pt'><span style="mso-spacerun: yes"> </span>nom du field 1</td> <td class=xl32 width=81 style='border-top:none;border-left:none;width:61pt'>F</td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl32 width=47 style='height:11.25pt;border-top:none; width:35pt' x:num>2</td> <td class=xl30 width=107 style='border-top:none;border-left:none;width:80pt'><span style="mso-spacerun: yes"> </span>fieldname2</td> <td class=xl32 width=81 style='border-top:none;border-left:none;width:61pt'>E</td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl32 width=47 style='height:11.25pt;border-top:none; width:35pt' x:num>3</td> <td class=xl30 width=107 style='border-top:none;border-left:none;width:80pt'><span style="mso-spacerun: yes"> </span>fieldname 3</td> <td class=xl32 width=81 style='border-top:none;border-left:none;width:61pt'>E</td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl32 width=47 style='height:11.25pt;border-top:none; width:35pt' x:num>3</td> <td class=xl30 width=107 style='border-top:none;border-left:none;width:80pt'><span style="mso-spacerun: yes"> </span>nom du field 3</td> <td class=xl32 width=81 style='border-top:none;border-left:none;width:61pt'>F</td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl32 width=47 style='height:11.25pt;border-top:none; width:35pt' x:num>4</td> <td class=xl30 width=107 style='border-top:none;border-left:none;width:80pt'><span style="mso-spacerun: yes"> </span>another fieldname 3</td> <td class=xl32 width=81 style='border-top:none;border-left:none;width:61pt'>E</td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl32 width=47 style='height:11.25pt;border-top:none; width:35pt' x:num>4</td> <td class=xl30 width=107 style='border-top:none;border-left:none;width:80pt'><span style="mso-spacerun: yes"> </span>outra nom du field 3</td> <td class=xl32 width=81 style='border-top:none;border-left:none;width:61pt'>F</td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'></td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'></td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'></td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'></td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'>Query1</td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'></td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl29 style='height:11.25pt'>ObjectID</td> <td class=xl29 style='border-left:none'>FieldName</td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl32 width=47 style='height:11.25pt;width:35pt' x:num>2</td> <td class=xl30 width=107 style='border-left:none;width:80pt'><span style="mso-spacerun: yes"> </span>fieldname2</td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl31 style='height:11.25pt'></td> <td></td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl33 colspan=4 style='height:11.25pt;mso-ignore:colspan'>SELECT FieldTable.ObjectID, FieldTable.FieldName</td> <td></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl33 colspan=2 style='height:11.25pt;mso-ignore:colspan'>FROM FieldTable</td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl33 colspan=5 style='height:11.25pt;mso-ignore:colspan'>WHERE (((FieldTable.ObjectID) Not In (SELECT FieldTable.ObjectID</td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl33 colspan=2 style='height:11.25pt;mso-ignore:colspan'>FROM FieldTable</td> <td class=xl31></td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <tr height=15 style='height:11.25pt'> <td height=15 class=xl33 colspan=3 style='height:11.25pt;mso-ignore:colspan'>WHERE (((FieldTable.LanguageCode)="F"));)));</td> <td colspan=2 style='mso-ignore:colspan'></td> </tr> <![if supportMisalignedColumns]> <tr height=0 style='display:none'> <td width=47 style='width:35pt'></td> <td width=107 style='width:80pt'></td> <td width=81 style='width:61pt'></td> <td width=56 style='width:42pt'></td> <td width=333 style='width:250pt'></td> </tr> <![endif]></table></body></html>
 
Upvote 0
That HTML didn't come out to good, here's the SQL:

SELECT FieldTable.ObjectID, FieldTable.FieldName
FROM FieldTable
WHERE (((FieldTable.ObjectID) Not In (SELECT FieldTable.ObjectID
FROM FieldTable
WHERE (((FieldTable.LanguageCode)="F")))));
 
Upvote 0
Thanks Norie,

But I don't think that's quite right. I was expecting a query that would determine the duplicates first in the NOT IN clause. Something like (having count>2) .

If I run the last part of your query, (ie - SELECT FieldTable.ObjectID
FROM FieldTable
WHERE (((FieldTable.LanguageCode)="F")))));

The first four rows returned are

row, objectid

1, 1
2, 1
3, 1
4, 1
5, 1

If I change the "F" to an "E" , I Get


row, object

1, 1
2, 1
3, 1
4, 1
5, 1
6, 1
7, 1

So technically I should get the following 3 rows for object id 3 that need to be translated

row, object

1, 1
2, 1
3, 1

But when I run the query you posted, I don't get anything for object_id 1 because it exists in the NOT IN clause.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Using VBA to change a single parameter in a long SQL statement
Replies
7
Views
1K
Micron
M
J
  • Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
262
Jagadeshrao
J
D
  • Question
Using Dynamic Arrays to Replicate ROW_NUMBER function in SQL server
Replies
5
Views
601
Dave Patton
D
J
  • Solved
Selecting individual cells
Replies
9
Views
202
jstuexcel
J
C
  • Solved
VBA macro: insert row if condition is met and apply formulae
Replies
3
Views
233
CXsjr
C

Forum statistics

Threads
1,221,831
Messages
6,162,242
Members
451,756
Latest member
tommyw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top