#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    12
    Rep Power
    0

    Cool Merging/Moving Column from T1 to T2


    I am not a pro at MySql. Having spent last few days trying to make it work I think I may need a bit of help.

    Two tables. Form submission date is in T1. T1 has 5 columns. T2 has 8 columns, and contains all other data. FormId column is common between the two tables. I need to move DateSubmitted column from T1 to T2 and then run SELECT query on resulting combined columns to display the data from now combined T1 & T2 tables. I cannot figure out how to move DateSubmitted column from T1 to T2 and then run SELECT query on resulting dataset. I tried UNION and JOIN suggestions I found here and on other forums but without success. Need help with constructing working query please!

    SELECT query for T2:

    SELECT
    SubmissionId,
    FieldName,
    FieldValue
    FROM
    #__submission_values
    WHERE
    FormId = 6
    AND
    FieldName IN ('field1','field2','field3','field4','field5','field6')
    ORDER BY SubmissionId ASC

    and this was also suggested, it runs but DateSubmitted is not displaying:

    SELECT t2.SubmissionId,
    t2.FieldName,
    t2.FieldValue,
    t1.DateSubmitted
    FROM table2 t2 INNER JOIN
    table1 t1 ON t2.FormId = t1.FormId
    WHERE t2.FormId = 6 AND
    t2.FieldName IN ('field1','field2','field3','field4','field5','DateSubmitted')
    ORDER BY t2.SubmissionId

    Thanks.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,298
    Rep Power
    7170
    A join is the correct approach.

    Based on what you've said, your second query should produce the correct result I think. Are you sure the DateSubmitted column contains a value?

    Also do you actually have a row in T2 with a field name of 'DateSubmitted'? I thought that value came from T1?

    Post CREATE statements for your two tables.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    12
    Rep Power
    0
    DateSubmitted in T1 has value, I can see date and time in every row.

    DateSubmited is a column in T1, it does not exist in T2, which is the root of my problem as T2 has all of the other data I am after.

    T1
    DateSubmitted FormId
    2013-02-01 6
    2013-02-02 6
    2013-02-03 6
    2013-02-04 6
    2013-02-05 6
    2013-02-06 6

    T2
    FormId FieldName FieldValue SubmissionId
    6 Field1 Value1 1
    6 Field2 Value2 2
    6 Field3 Value3 3
    6 Field4 Value4 4
    6 Field5 Value5 5
    6 Field6 Value6 6
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    12
    Rep Power
    0
    Issue is resolved. Thanks.

IMN logo majestic logo threadwatch logo seochat tools logo