inserting two tables worth of data into one table where each has where condition

I have two tables that I would like to select rows from each having specific where clauses. I want to take that data and put it in one table. I have tried two methods but am getting errors on each of the methods. What is the solution? I can not use a left JOIN because there isnt anything to JOIN them on

CROSS JOIN ATTEMPT – fails with Two Where clauses

INSERT IGNORE INTO reser (wait_id, name, party_size, phone, email, Time_stamp, status, Table_id, FOH_Table_number, Table_type, Staff_First_Name, Staff_Last_Name ) 

SELECT a.wait_id, a.name, a.party_size, a.phone, a.email, a.Time_stamp, a.status, b.Table_id, b.FOH_Table_number, b.Table_type, b.Staff_First_Name, b.Staff_Last_Name

FROM list a HAVING wait_id='122'
cross join Tabl b WHERE id='122'

and then the nested method produces error – incorrect number of columns

INSERT IGNORE INTO reser
(wait_id, name, party_size, phone, email, Time_stamp, status, Table_id, FOH_Table_number, Table_type, Staff_First_Name, Staff_Last_Name ) 
VALUES(
    
(SELECT wait_id, name, party_size, phone, email, Time_stamp, status  FROM list WHERE wid='122'),

(SELECT Table_id, FOH_Table_number, Table_type, Staff_First_Name, Staff_Last_Name FROM Tbl Where id = '2')
    )

Answer

In your cross join method, you have to use WHERE clause as follows:

INSERT IGNORE INTO reser (wait_id, name, party_size, phone, email, Time_stamp, status, Table_id, FOH_Table_number, Table_type, Staff_First_Name, Staff_Last_Name ) 
SELECT a.wait_id, a.name, a.party_size, a.phone, a.email, a.Time_stamp, a.status,
       b.Table_id, b.FOH_Table_number, b.Table_type, b.Staff_First_Name, b.Staff_Last_Name
FROM list a CROSS JOIN 
cross join Tabl b 
WHERE a.wait_id='122' and b.id='2'

Advertisement

Answer