SQL join help

[Steve Workings]

Lack of SQL know-how here, I think.

I set up a grid with a select statement that includes a right outer join. This means I display all records in TableA, but only matching records in TableB.

The grid displays exactly what I want, and no other join configuration gets me what I want.

But, when I try to enter records in TableB, the grid returns this error:

Record was not updated because it has been deleted by another user

Dunno what to do about this. Any ideas?

[chadbrown]

Re: SQL join help

Right outer I thought was all of table b and any matching table a

[Steve Workings]

Re: SQL join help

Well, I've tried both left and right. There's a nice little helper in the SQL genie that helps me at least get and display the right data.

But the result is the same in either case - I get the same error message.

[Pat Bremkamp]

Re: SQL join help

Steve,

Try building the join into a view, then base the grid on the view instead of an SQL statement. That would be easier for Alpha to deal with.

Pat

[phannon]

Re: SQL join help

Can I see some sequel code

[Steve Workings]

Re: SQL join help

Well, again my SQL falls short. I don't know how to create a view. I think I need something like this:

CREATE VIEW English_to_Translate AS
SELECT English_ID AS English_ID1, Word FROM lang_englishmaster
SELECT Language_id, English_ID AS English_ID2, Translate_ID, Translate, Available FROM lang_translation
LEFT OUTER JOIN lang_englishmaster lang_translation
ON English_ID = English_ID;


Any help appreciated.

[Steve Workings]

Re: SQL join help

I took Pat's advice, and got some help from Chad, and still no joy. I set up view with a left outer join, and still get the same error when working with the grid.

I'm using Toad to help with MySQL. When I try to enter data in the View via Toad, my error is:

Can not modify more than one base table through a join view 'test.eng_to_tran'

So, I guess I'm back to a basic question:

I have TableA that is a master list of words.

TableB will be the list of words in another language.

I'd like to have a grid that displays all the records in TableA, and gives the user a field to enter the translated word into TableB.

This should be simple, but so far no solution. Any ideas?

If anyone finds it useful, here's my SQL to create the view:

CREATE VIEW {{Unquote(:Table_Name)}} AS
SELECT lang_englishmaster.English_ID AS EnglsihID1
, lang_englishmaster.Word
, lang_translation.Language_id
, lang_translation.English_ID AS EnglishID2
, lang_translation.Translate_ID
, lang_translation.Translate
, lang_translation.Available
FROM
test.lang_englishmaster lang_englishmaster
LEFT OUTER JOIN
test.lang_translation lang_translation
ON (lang_englishmaster.English_ID = lang_translation.English_ID)
ORDER BY lang_englishmaster.Word ASC

[Atta]

Re: SQL join help

You can't insert into view ! but can update, as far as I know.

Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
I found this here: http://technet.microsoft.com/en-us/l.../ms187956.aspx

[glenschild]

Re: SQL join help

I took Pat's advice, and got some help from Chad, and still no joy. I set up view with a left outer join, and still get the same error when working with the grid.

I'm using Toad to help with MySQL. When I try to enter data in the View via Toad, my error is:

Can not modify more than one base table through a join view 'test.eng_to_tran'

So, I guess I'm back to a basic question:

I have TableA that is a master list of words.

TableB will be the list of words in another language.

I'd like to have a grid that displays all the records in TableA, and gives the user a field to enter the translated word into TableB.

This should be simple, but so far no solution. Any ideas?

If anyone finds it useful, here's my SQL to create the view:

CREATE VIEW {{Unquote(:Table_Name)}} AS
SELECT lang_englishmaster.English_ID AS EnglsihID1
, lang_englishmaster.Word
, lang_translation.Language_id
, lang_translation.English_ID AS EnglishID2
, lang_translation.Translate_ID
, lang_translation.Translate
, lang_translation.Available
FROM
test.lang_englishmaster lang_englishmaster
LEFT OUTER JOIN
test.lang_translation lang_translation
ON (lang_englishmaster.English_ID = lang_translation.English_ID)
ORDER BY lang_englishmaster.Word ASC

How about using a trigger to create a record in table b automatically when a record is created in table a. Allow null values in the translated field. Then when the view is used you are updating an existing record rather than trying to create a new record?

There is also a typo in the sql you posted on line 2 for the alias name?

[Steve Workings]

Re: SQL join help

OK, ya'll are confirming what I thought - I need to re-think my approach. Should be easy for me but kinda overloaded and as inventive as usual I guess.

[RMarmion]

Re: SQL join help

Steve,
You have probably figured out a solution by now, but maybe this will help:
Updating a view suggested by Atta is correct - you can, but only one side OR the other but not both.
Adding through a view is not possible because if you think about the two tables side by side, if there isn't a record on side B, there is nowhere for the data to go.
Glenshild suggested creating the B side record when you create the A side record, but I'm assuming that you are building multiple B side tables to be able to translate multiple languages, so you can't be triggering new records in all the possible B side language record sets - not efficient - and not to mention that when you add a new language set to the B side, you'd have to pre-create all those B side records.
Couple of solutions here:
One solution I can think of is a combination of both suggestions from Atta and Glenshild. Use the view but add an Add button to the B side so that when you click it it will run an SQL insert to create the B side record and then you will have a container on the B side for your data, then refresh the underlying query (or does Ajax do that automatically for you?).
Second one would be to add a Detail View to the grid, allowing Add (or maybe only Add), pre-populate the EnglishID key field with the A side key field and only select B side fields to be displayed in the Detail View. FYI, this second approach is pure hypothesis as I haven't actually done this!
As a variation on either version, can you hijack the save button using the java events and make it run a manual SQL insert with the new data?
A third possibility is to implement the B side as a Drop Down Box and enter Not in List Rule as the B side. Again hypothesis as I'm not sure how you would make sure that you create the new Drop Down Record with the EnglishID key.
-Rob
p.s. I'll trade you SQL work by me for AlphaFive Web coaching from you if you'd be interested.

[Steve Workings]

Re: SQL join help

This should be simple:

INSERT INTO lang_workarea(english_id,word)
   SELECT english_id,word FROM lang_englishmaster

Both tables have identical fields. Yet it fails, and I don't know why. Help?

[workaholic06]

Re: SQL join help

Steve,

Check the error being returned. Try the following code:

Dim conn as SQL::Connection
Dim strSQL as C

Dim flag as L
Dim strResult as C

conn.TraceSQL=.T.
flag = conn.open("::name::myconnection")  
if (flag == .f.) then
	ui_msg_box("ERROR","Unable to open connection: conn",UI_ATTENTION_SYMBOL+UI_OK)
	end
end if

strSQL = "INSERT INTO lang_workarea(english_id,word) SELECT english_id,word FROM lang_englishmaster"
flag=conn.Execute(strSQL)
strResult = conn.CallResult.NativeText
if (flag == .f.) then
    	ui_msg_box("SQL Error","Failed: "+strResult,UI_OK)
end if

conn.close()

Thanks,

Robert

[chadbrown]

Re: SQL join help

Steve what is the where statement?

This should be simple:

INSERT INTO lang_workarea(english_id,word)
   SELECT english_id,word FROM lang_englishmaster

Both tables have identical fields. Yet it fails, and I don't know why. Help?

[workaholic06]

Re: SQL join help

Steve what is the where statement?

I don't think he had a WHERE clause - looks like he was transferrinig all the records.

My bet is that the table lang_workarea already has entries, and he's duplicating the english_id value with another record. Of course, another assumption would be that his english_id column has an index of Primary or Unique.