Search code examples
mysqlcreate-table

mysql : Can I recreate a table using select, innerjoin and where clause in a single step?

I am new to using mysql workbench, and really need a simple data extraction script (post which I will be back in the land of the free i.e. python)

The idea is to create a new table by joining two pre-existing tables on an id, and applying a where clause. IS the following alright? or do I need to first apply the where clause and then do the create and join?

Here is attempt number 1 (which did not work).

CREATE TABLE `mysql`.`new_tbl`(
SELECT a.* , b.*
FROM mysql.tbla a INNER JOIN mysql.tblb b
ON a.id = b.id
where b.category='Z'
);
Solution

  • You can use views, I think it most approaches what you're idealizing.
    You'll find syntax details here.

    For your specific situation, you shall run

    CREATE OR REPLACE VIEW view_name AS
SELECT a.*, b.* FROM mysql.tbla AS a
INNER JOIN mysql.tblb AS b
  ON (a.id = b.id)
WHERE b.category = 'Z';