Hi,

I am have a MySQL database and need to insert/update a table (T3) using data from two other tables (T1 & T2).

T3 should have 2 columns (PID & Name) which match 2 columns in T1, with additional columns based on the row values of T2. A simple "insert" can add the rows from T1 to T3 for matching columns, but the problem I am having is getting T2 data into T3.

T2 essentially has 3 columns (PID, Field, Data). The PID matches the PIDs in T1, the Field is a variable of about 15 different field identifiers, and the Data is the value for that Field for that PID. What I need to do is add the Field values as column names in T3 (so T3 would end up with 2 + 15 columns) and then update the row value of each Column according to the Data value matchin the PIC in T2. Here is a simplified graphical representation:

----- T1 -------
PID Name
1 Bob
2 Jerry
3 Jane
----------------

---- T2 -----------------------
PID Field Data
1 Address 1 address
1 Phone 111111111
1 Website www.a.com
2 Phone 222222222
2 Address 2 address
3 Address 3 address
3 Website www.b.com
------------------------------------------

Need to combine these into T3 table:
------------------ T3 -------------------------------------
PID Name Address Phone Website
1 Bob 1 address 11111111 www.a.com
2 Jerry 2 address 22222222
3 Jane 3 address www.b.com
-----------------------------------------------------------

As there are limited "Field" types in T2, I could set up the columns in T3 first if necessary.

I would be grateful for any help on this.

Regards, Richard.

Something like this should be OK:
You didn't mention what would happen if the PID is no longer in T1 - do you mark it as deleted or do you keep it? You could also make a view called T3 and build the data from T1 and T2 but it depends on how large these tables are.

Just curious but how did you end up in China writing SQL?

Mike

Mike, thanks for that.

I could be mistaken (actually this is likely as this is just a part time thing for me) be I don't think it helps with converting the single column values (from T2 "field" column) into fields themselves (in T3), and then using the corresponding PID values (from T2) to update the newly created, empty fields in T3 with the corresponding "data" column values (from T2).

Unless I am mistaken, your example is more a column to column insert/update solution (this works for T1, but not T2).

I have been in China for 10 years actually (originally from Australia). I have done a few different database projects over the years for different employers and normally have to relearn from scratch each time as once I put it down it is easily forgotten. This one is a website for English speaking expats like myself to get news, events, forums, and other information about Nanjing (where I live). I think a site like this will really improve the livability here as info in English is pretty hard to come by... check it out if you have time: Nanjing Expat.

Richard.

I'm afraid I don't understand what the issue is - can you explain the issue and/or provide some example data to illustrate the problem for us?

Mike,

I have attached the data samples from the two tables (in table2 you can't see the column "itemid" but it is there as well and the values match entries in table1).

Basically Table1 is the format I want, except I need more columns that in this case would be called:
"address" (where table2.fieldid = 1),
"website" (where table2.fieldid = 8),
"phone" (where table2.fieldid = 10),
etc...

So I could get away without a 3rd table if I could find a way to add the columns (this could be manual if necessary), then update the new column values ("address", "website", "phone" etc.) with "data_txt" from table2 where the itemid in table2 = the itemid in table1.

My current solution I am working on is this:
---------------------------------------------------------------------------
CREATE TABLE `djos_item_data` (
`itemid` int(11) default NULL,
`title` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`website` varchar(255) NOT NULL
) as
select `itemid`,`title`,
(select `data_txt` from `cjos_sobi2_fields_data` where `ajos_sobi2_item.itemid` = `cjos_sobi2_fields_data.itemid` and `cjos_sobi2_fields_data.fieldid` = '1') as `address`,
(select `data_txt` from `cjos_sobi2_fields_data` where `ajos_sobi2_item.itemid` = `cjos_sobi2_fields_data.itemid` and `cjos_sobi2_fields_data.fieldid` = '10') as `phone`,
(select `data_txt` from `cjos_sobi2_fields_data` where `ajos_sobi2_item.itemid` = `cjos_sobi2_fields_data.itemid` and `cjos_sobi2_fields_data.fieldid` = '8') as `website`,
from `ajos_sobi2_item`
---------------------------------------------------------------------------

the above query results in a an error in phpMyAdmin:
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from `ajos_sobi2_item`' at line 12"

Any ideas?

Mike, thanks for all your help, but actually I have just got the answer to this. Code as follows... hope it helps someone:
-----------------------------------------------------------------------------
CREATE TABLE `djos_item_data` (
`itemid` int(11) default NULL,
`title` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`website` varchar(255) NOT NULL
) as
select `itemid`,`title`, (
select `data_txt`
from `cjos_sobi2_fields_data`
where `ajos_sobi2_item`.`itemid` = `cjos_sobi2_fields_data`.`itemid` and `cjos_sobi2_fields_data`.`fieldid` = '1'
) as `address`, (
select `data_txt`
from `cjos_sobi2_fields_data`
where `ajos_sobi2_item`.`itemid` = `cjos_sobi2_fields_data`.`itemid` and `cjos_sobi2_fields_data`.`fieldid` = '10'
) as `phone`, (
select `data_txt`
from `cjos_sobi2_fields_data`
where `ajos_sobi2_item`.`itemid` = `cjos_sobi2_fields_data`.`itemid` and `cjos_sobi2_fields_data`.`fieldid` = '8'
) as `website`
from `ajos_sobi2_item`
-----------------------------------------------------------------------------

Must admit I can't see what was wrong with my original method but, as long as you have a solution, that's fine

Mike

Mike, looking at it again you are probably right. I put it down to my inexperience with SQL.

Thanks for the help and for future visitors both solutions might be options. Richard.