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
x
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')
)
Advertisement
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'