Re: Request for help. Im a beginner.
>The best would be if i could add the projectname to the addresstable
Close. Add the project_id to the address table, then query with a join on the project id.
Like this.
DROP TABLE IF EXISTS projectname;
CREATE TABLE projectname (
proj_id int(10) unsigned NOT NULL AUTO_INCREMENT,
projectname varchar(50) NOT NULL,
PRIMARY KEY (proj_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table projectname */
insert into projectname(proj_id,projectname) values
(1,'WestConnex'),
(2,'SouthConnex'),
(3,'NorthConnex');
DROP TABLE IF EXISTS projectaddress;
CREATE TABLE projectaddress (
address_id int(10) unsigned NOT NULL AUTO_INCREMENT,
proj_id int(10) unsigned DEFAULT NULL,
projectaddress varchar(128) DEFAULT NULL,
PRIMARY KEY (address_id),
KEY FK_proj_id (proj_id),
CONSTRAINT FK_proj_id FOREIGN KEY (proj_id) REFERENCES projectname (proj_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table projectaddress */
insert into projectaddress(address_id,proj_id,projectaddress) values
(1,1,'Western Sydney to Tempe'),
(2,3,'Wahroongah to Pennant Hills');
Get your data.
mysql> SELECT p.projectname
-> , a.projectaddress
-> FROM projectname p
-> JOIN projectaddress a
-> ON p.proj_id=a.proj_id;
+-------------+-----------------------------+
| projectname | projectaddress |
+-------------+-----------------------------+
| WestConnex | Western Sydney to Tempe |
| NorthConnex | Wahroongah to Pennant Hills |
+-------------+-----------------------------+
2 rows in set (0.00 sec)
If you want all the projects, even those without an address, use a LEFT JOIN.
mysql> SELECT p.projectname
-> , a.projectaddress
-> FROM projectname p
-> LEFT JOIN projectaddress a
-> ON p.proj_id=a.proj_id;
+-------------+-----------------------------+
| projectname | projectaddress |
+-------------+-----------------------------+
| WestConnex | Western Sydney to Tempe |
| NorthConnex | Wahroongah to Pennant Hills |
| SouthConnex | NULL |
+-------------+-----------------------------+
3 rows in set (0.00 sec)
Good luck,
Barry.