CODEWITHSUNDEEP
mysqljoinleft-joininner-joincreate-table
Paul Seattle
Paul Seattle

Reputation: 491

Create a new table from two existing tables

I need to add a new column cover_art, to one of the tables that I referenced news when I created a new table newsdetails using JOIN but I can't remember how I did it.

Here's the table that includes the id artistid that is referenced in the the artists table:

CREATE TABLE `news` (
`post_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`artistid` smallint(5) unsigned NOT NULL,
`title` varchar(150) NOT NULL,
`cover_art` varchar(150) NOT NULL,
`blog_entry` text NOT NULL,
`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`rating` varchar(150) NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=76 ;


INSERT INTO `news` (`post_id`, `artistid`, `title`, `cover_art`, `blog_entry`,     `updated`, `rating`) VALUES
(1, 1, 'Ultra', 'images/temp_cover.jpg', 'The very first track Barrel Of A Gun is blah blah.', '2012-11-19 16:23:19', '990,568'),
(2, 13, 'Mish Mash', 'images/temp_cover.jpg', 'A train pulls into a station and etc etc ad infinitum.', '2012-11-19 16:23:44', '831,880'),

And here's the artists table that has the id that I include in the news:

CREATE TABLE `artists` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`artists_name` varchar(150) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=170 ;


INSERT INTO `artists` (`id`, `artists_name`) VALUES
(1, 'Depeche Mode'),
(2, 'Amon Tobin'),
Etc.

I've looked into doing inner and left joins but can't for the life of me remember what I did to create this newsdata table that turns artistid into artists_name:

1 post_id smallint(5) UNSIGNED No 0
2 title varchar(150) latin1_swedish_ci No None
3 blog_entry text latin1_swedish_ci No None
4 updated timestamp No 0000-00-00 00:00:00
5 rating varchar(150) latin1_swedish_ci No None
6 artists_name varchar(150) latin1_swedish_ci No None

And thanks to eggyal for letting me know what FOREIGN KEYS are - i.e. NOT for doing this :-(

Upvotes: 0

Views: 645

Answers (1)

eggyal
eggyal

Reputation: 126035

SELECT news.post_id,
       news.title,
       news.blog_entry,
       news.updated,
       news.rating,
       artists.artists_name
FROM   news JOIN artists ON artists.id = news.artistid

Upvotes: 0

Related Questions