pld51
asked on
How to create SQL recordset for this table
We have a table tblTranslate, with following columns:-
TID (ie index), AgentID, WordID, Word
Agent 1 provides the master words as Word, each with different WordID. Other agents translate these.
I have difficulty creating the recordset that shows all the words registered by Agent 1, and the translations created by another agent, say agent 28. The recordset should show both words that have been translated by agent 28, and those not yet translated. This recordset would be the basis for a form for the agent to enter/update etc.
Grateful for help. Thanks
TID (ie index), AgentID, WordID, Word
Agent 1 provides the master words as Word, each with different WordID. Other agents translate these.
I have difficulty creating the recordset that shows all the words registered by Agent 1, and the translations created by another agent, say agent 28. The recordset should show both words that have been translated by agent 28, and those not yet translated. This recordset would be the basis for a form for the agent to enter/update etc.
Grateful for help. Thanks
ASKER
Thanks. In trying it by converting to our real case I realized I did not understand the approach enough, with the As presumably acting as copies of the table. You had A1, A2 and A28 so I was not sure how to apply.
Could you please rephrase for the real situation, so I can re-test? The table name is tblTranslateNew, WebAgentID should replace AgentID, the base agent is 28 and the other agent say is 1045. This can then be directly tested.
Could you please rephrase for the real situation, so I can re-test? The table name is tblTranslateNew, WebAgentID should replace AgentID, the base agent is 28 and the other agent say is 1045. This can then be directly tested.
ASKER
OK, I got this working after changing last line to AND A28.AgentID = 28.
It shows all the values of Agent1 for which Agent28 has made a translation. It does not show the untranslated values, despite being a left join. How to modify so it shows both the translated and untranslated values?
It shows all the values of Agent1 for which Agent28 has made a translation. It does not show the untranslated values, despite being a left join. How to modify so it shows both the translated and untranslated values?
It should list all the words that Agent1 added to the list... Can you do a cross check by just selecting the word added to the list by Agent1 as separate query and check the count with the one returning from my query... they should be equal
ASKER
Agree, I am baffled too. I ran this on a simplified copy of the table, tblTranslateNewCopy, that has 10 records of Agent1, 7 of Agent28 (see table listing in the code section).
So the select query below, based on yours, should show 10 records. But only 7 show, those translated by 28.
SELECT A1.Word, A1.Type, A1.WebAgentID, A2.Word AS AgentWord, A2.Type AS Agent, A2.WebAgentID AS AgentLang
FROM dbo.tblTranslateNewCopy A1 LEFT OUTER JOIN
dbo.tblTranslateNewCopy A2 ON A1.WordID = A2.WordID
WHERE (A1.WebAgentID = 1) AND (A2.WebAgentID = 28)
So the select query below, based on yours, should show 10 records. But only 7 show, those translated by 28.
SELECT A1.Word, A1.Type, A1.WebAgentID, A2.Word AS AgentWord, A2.Type AS Agent, A2.WebAgentID AS AgentLang
FROM dbo.tblTranslateNewCopy A1 LEFT OUTER JOIN
dbo.tblTranslateNewCopy A2 ON A1.WordID = A2.WordID
WHERE (A1.WebAgentID = 1) AND (A2.WebAgentID = 28)
WebTID WordID WebAgentID Word
171 1 28 pricexxx
172 2 28 pricesxxx
173 3 28 per personxxx
174 4 28 Galapagosxxx
175 5 28 Galapagos yachtxxx
176 6 28 yachtxxx
177 7 28 yachtsxxx
315 1 1045 F5=0
316 2 1045 F5=K
317 3 1045 =0 G5;>25:0
318 4 1045 0;0?03>AA:85 >AB@>20
319 5 1045 0;0?03>AA:0O OEB0
411 1 1 price
412 2 1 prices
413 3 1 per person
414 4 1 Galapagos
415 5 1 Galapagos yacht
416 6 1 yacht
417 7 1 yachts
418 8 1 class
419 9 1 category
420 10 1 sailing
ASKER
Quick note in case it is confusing: the words for agent 1045 are all in Russian; they appear as gobbledygook above.
ASKER
This is simpler version of the select statement, without the Type field.The resulting recordset has only 7records, ie the translated words.
I think the problem is the where clause, WHERE (A1.WebAgentID = 1) AND (A2.WebAgentID = 28). This seems to negate the left join as it requires both to be true. Thus untranslated words are not included.
I think the problem is the where clause, WHERE (A1.WebAgentID = 1) AND (A2.WebAgentID = 28). This seems to negate the left join as it requires both to be true. Thus untranslated words are not included.
SELECT A1.Word, A1.WebAgentID, A2.Word AS AgentWord, A2.WebAgentID AS Agent
FROM dbo.tblTranslateNewCopy A1 LEFT OUTER JOIN
dbo.tblTranslateNewCopy A2 ON A1.WordID = A2.WordID
WHERE (A1.WebAgentID = 1) AND (A2.WebAgentID = 28)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks pcelba, the first works perfectly, the second only gives the translated values. I tried the second route before, but there are no null values in the table. So the first it is. Thanks again.
The first way looks simpler.
The second way must work correctly because the WHERE condition does not evaluate records from input tables but the join result. NULL condition is valid for records from table A1 having no counterparts in table A2.
Probably better implementation would be to check NULL value in column used for joining:
WHERE .... (A2.WebAgentID = 28 OR A2.WordID IS NULL)
but if A2.WebAgentID does not contain NULL values then the first condition is also correct.
The second way must work correctly because the WHERE condition does not evaluate records from input tables but the join result. NULL condition is valid for records from table A1 having no counterparts in table A2.
Probably better implementation would be to check NULL value in column used for joining:
WHERE .... (A2.WebAgentID = 28 OR A2.WordID IS NULL)
but if A2.WebAgentID does not contain NULL values then the first condition is also correct.
ASKER
Noted, and thanks again!
I got this all working fine in a stored procedure, but then hit the next (and hopefully last) hurdle. How to take out the translated values from the recordset for use in the pages that use translated values (or the original English word if the the translated word is null).
I thought this was easy, but spent fruitless hours. Have put it as a separate question, if you can help, great:- https://www.experts-exchange.com/questions/25024913/Generate-variables-from-SQL-recordset-using-classic-ASP-VBScript.html
I got this all working fine in a stored procedure, but then hit the next (and hopefully last) hurdle. How to take out the translated values from the recordset for use in the pages that use translated values (or the original English word if the the translated word is null).
I thought this was easy, but spent fruitless hours. Have put it as a separate question, if you can help, great:- https://www.experts-exchange.com/questions/25024913/Generate-variables-from-SQL-recordset-using-classic-ASP-VBScript.html
Open in new window