MySQL Forums
Forum List  »  Newbie

Re: Request for help. Im a beginner.
Posted by: Barry Galbraith
Date: November 25, 2016 05:48PM

>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.

Options: ReplyQuote


Subject
Written By
Posted
November 25, 2016 11:50AM
Re: Request for help. Im a beginner.
November 25, 2016 05:48PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.