Concantate Fields from Three tables

Discussion in 'Questions (Windows Mobile)' started by Kintara, Jul 24, 2015.

  1. Kintara

    Kintara Member Licensed User

    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 :cool:
     
  2. RandomCoder

    RandomCoder Well-Known Member Licensed User

    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.
     
  3. Kintara

    Kintara Member Licensed User

    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:
    Code:
    '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.
     
    Last edited: Jul 26, 2015
Loading...