In drupal,the content type table and my own table is used to do some operations. Im having two tables namely Table1:'content_field_mem_id' & Table2:'Ex' description of my content_field_mem_id is
+--------------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| vid | int(10) unsigned | NO | PRI | 0 | |
| nid | int(10) unsigned | NO | MUL | 0 | |
| field_mem_id_value | int(11) | YES | | NULL | |
| sid | int(30) | YES | MUL | NULL | |
+--------------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
The member Id values are inside the field_mem_id_value field. Another Table Ex description is shown below :
mysql> desc Ex;
+---------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+----------------+
| sid | int(30) | NO | PRI | NULL | auto_increment |
| mem_id | varchar(30) | YES | | NULL | |
| nature_of_due | tinytext | YES | | NULL | |
| amount | int(30) | YES | | NULL | |
| date | date | YES | | NULL | |
| paidby | tinytext | YES | | NULL | |
+---------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
The above table Ex doesnt contain any values.i.e)the Ex table has 0 rows. After posting operation by the admin Ex table will gets affected.
My aim is to show the view to the user using the Ex table.
The content_field_mem_id table consists 1000 numbers of member id which was imported by me and inserted into that content_field_mem_id table.
The relationship between this two table is one to many relationship i.e) Each member can pay any of the fees . But member fees is must so one member can have many transactions on the second table.
How to join the both tables and how to set the foreign key constraints?. I have set the foreign constraint for the mem_id ,it throws me an error not an syntax error.
I want to join the tables and the second table has to be taken for the view purpose and second table member id can be duplicated cos of many transactions.