0

This is may sample database 

TABLE A-----> TABLE B <-----TABLE C

The arrows show the relationship

TABLE A 
{ 
   tblA_col1 <----primary key
} 
TABLE A
 (tblA_col1)
  | A_1  |<----data


TABLE C 
{ 
  tblC_col1 <----primary key
}
TABLE C
 (tblC_col1)
  | C_1  |<----data
  | C_2  |
  | C_3  |


TABLE B
{
  tblB_col1 <----primary key
  tblA_col1 <----col from table A
  tblC_col1 <----col from table C
}
TABLE B
(tblB_col1 |  tblA_col1  |  tblC_col1)
|   B_1    |     A_1     |    C_1    |
|   B_2    |     A_1     |    C_2    |
|   B_3    |     A_1     |    C_3    |

Now my problems is how to insert the data in TABLE B with just one query?

shA.t
  • 16,580
  • 5
  • 54
  • 111
hPys
  • 141
  • 2
  • 4
  • 14

2 Answers2

1
insert into B (tblA_col1, tblC_col1 )
select A.tblA_col1, C.tblC_col1 from 
A, C;

Is this what you wanted?

Akhil
  • 2,602
  • 23
  • 36
  • yeah thanks, I tried it before but it didn't work..then i try it again it works! – hPys May 03 '15 at 09:09
  • Wow I thought It will not be possible. Never tried that though.But I think its fine and works. – Shaiful Islam May 03 '15 at 09:16
  • yeah it work, and now my problem is how to convert it to insert data in Codeigniter – hPys May 03 '15 at 09:30
  • Well you know the query.So you can use `$this->db->query($query);` – Shaiful Islam May 03 '15 at 09:46
  • got an error public function insert_req($s_no, $filed, $dat) { $sql = "Insert into student_requirements (filed, datesubmitted, documents, r_no, s_no)" . "($filed, $dat, (Select a.description, a.r_no, b.s_no from requirements a, student b where s_no = $s_no))"; return $sql->result(); } what can be done to this? – hPys May 03 '15 at 09:51
  • can i put select statement in insert into table B(col1, col2 col3) values (va1, val2, select statement)???? – hPys May 03 '15 at 09:54
0

Please refer to this thread as the answer is related with your problem.

MySQL doesn't support multi-table insertion in a single INSERT statement

However, you CAN use a transaction and have both of them be contained within one transaction

sql - insert into multiple tables in one query

Community
  • 1
  • 1
DigitalDouble
  • 1,747
  • 14
  • 26