Concantate Fields from Three tables

[Kintara]

Hi Everyone. Me again.
I have three tables in the SQLite3 database arranged as so:
Table1 Name :Stores
Field1: Item
Field2: Description
Field3: Bin
Field4: Notes

Table2 Name: Vocab
Field1: Material_Code
Field2: Basic_Text

Table3 Name: Corrections
Field1: Item
Field2: Notes

Field1 in all three table will be the unique ID and will be used as the common reference for an item across all three tables.


I would like to add Vocab.Basic_Text and Corrections.Notes to Stores.Notes where Stores.Item for the same Item
Pseudo cade:
UPDATE Stores SET Stores.Notes = Stores.Notes & ' ' & Corrections.Notes & ' ' & Vocab.Basic_Text where Stores.Item=Vocab.Material_Code = Corrections.Item

Input:-
Stores
Item Description Bin Notes
1690191 Belt A21
1690255 Roller C23
1695399 Lamp Q43

Vocab
Material_Code Basic_Text
1690191 5mm x 289mm Polycord
1690255 6mm x 300mm Flat

Corrections
Item Notes
1690255 Used in Leveller section


Output:-
Stores
Item Description Bin Notes
1690191 Belt A21 5mm x 289mm Polycord
1690255 Roller C23 Used in Leveller section 6mm x 300mm Flat
1695399 Lamp Q43

I have tried using joins and unions and concatenation but cannot get it right.
Does anyone have the knowledge to help me do this?


(sorry, the alignment of the columns does not show as I had intended)
Kintara

 

[RandomCoder]

I'm no expert on SQL but I've had plenty of experience administrating a large database. The way I would do this would be to query your dataset first using a simple SELECT statement to get the values you want and store these in temporary variables in your program, then run the UPDATE command and SET Stores.Notes with the variables.
There maybe a way to do this in one single statement but breaking it up into two will be simpler and probably make your code easier to follow.

 

[Kintara]

Too much of a challenge for me. I could not solve it so I reworked the system.
It now creates two table in the Database called 'Vocab' and 'Corrections'. These are populated from two csv files "Vocab.csv" & "03 Corrections.csv".

During runtime, the two tables are queried and results used to build up a string to display all relevant fields.

Code snippet:

'ItemCode is the Unique ID to look for in each of the two tables
Description=ItemCode 'Description is a string that will be built up the full description from the two Tables

'Add Notes from Corrections file
'Item is the Unique ID in the Corrections Table
Cursor1 = Main.SQLStoresImages.ExecQuery("SELECT * FROM Corrections WHERE Item = '" & ItemCode & "'")
If Cursor1.RowCount>0 Then
    Cursor1.Position=0
    Description =Description & CRLF & Cursor1.GetString("Description")& CRLF & Cursor1.GetString("Notes")
End If
Cursor1.Close

'Add SDMM_DESCRIPTION & BASIC_TEXT from Vocab file
'MATERIAL_CODE is the Unique ID in the Vocab Table
Cursor1 = Main.SQLStoresImages.ExecQuery("SELECT * FROM Vocab WHERE MATERIAL_CODE = '" & ItemCode & "'")
If Cursor1.RowCount>0 Then
    Cursor1.Position=0
    Description =Description & CRLF & Cursor1.GetString("SDMM_DESCRIPTION")& CRLF & Cursor1.GetString("BASIC_TEXT")
End If
Cursor1.Close   
   
Description =Description & CRLF & CRLF & "Click To Close Text Window"
‘Label9 is the Label view that will show the Description text
Label9.Text=Description

Thanks anyway.