0

how can I create a SQL statement to create a table that has columns from 4 tables? So in my new created table I would like to extract the Id, Product Title, Items in cart(number) (from these 4 tables so it will be 4 columns with Items in cart (number)), Link. These tables have 3024 rows so how can I extract these information in one table? I attached the tables(SQL) and the table(SQL) that I would like to have(I would like a SQL command to do that an insert or join). I will appreciate any help.

DROP TABLE IF EXISTS `csv_10_05`;
CREATE TABLE IF NOT EXISTS `csv_10_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `csv_11_05`;
CREATE TABLE IF NOT EXISTS `csv_11_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `csv_12_05`;
CREATE TABLE IF NOT EXISTS `csv_12_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `csv_13_05`;
CREATE TABLE IF NOT EXISTS `csv_13_05` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `product price` varchar(55) NOT NULL,
  `items in cart` varchar(2020) DEFAULT NULL,
  `items in cart(number)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

And this is what I want (now I've created manually but I would like a query like join to extract the columns)

DROP TABLE IF EXISTS `amazon_stock`;
CREATE TABLE IF NOT EXISTS `amazon_stock` (
  `id` int(50) NOT NULL AUTO_INCREMENT,
  `product title` varchar(2040) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `items in cart(10_05)` varchar(50) DEFAULT NULL,
  `items in cart(11_05)` varchar(50) DEFAULT NULL,
  `items in cart(12_05)` varchar(50) DEFAULT NULL,
  `items in cart(13_05)` varchar(50) DEFAULT NULL,
  `link` varchar(2024) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
4
  • Are you only going to be selecting data and not inserting? Your final table might be better suited to a view.
    – Mark D
    Commented Jun 6, 2018 at 3:13
  • I think is better an INSERT statement
    – ryy77
    Commented Jun 6, 2018 at 3:56
  • One table per month? Not wise. No indexes? Not wise.
    – Rick James
    Commented Jun 8, 2018 at 3:22
  • To continue Rick's list, space in identifiers, MyISAM Commented Jun 8, 2018 at 4:00

1 Answer 1

0
  1. Create a table with an extra column to distinguish where it came from
  2. Do something like

...

INSERT INTO new_table (title, price, items_ct, link, which)
    ( SELECT *, '10_05' AS which FROM csv_10_05 )
    UNION ALL
    ( SELECT *, '11_05' AS which FROM csv_11_05 )
    UNION ALL
    ...
1
  • Thank you for your answer. I can't give you a vote because i don't have enough reputation but i marked as a correct answer
    – ryy77
    Commented Jun 8, 2018 at 3:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.