MYSQL - JOIN 2 Tables to get a sorted result-CodePudding

I am using this query to import all the 'id_order'

INSERT INTO `gf_compta` (ID)
SELECT `gf_order_detail`.id_order
FROM `gf_order_detail`;

But I would like to get only the order which 'current_state=4' in the table gf_orders

To make it even simplier, I was wondering something like this:

INSERT INTO `gf_compta` (ID)
SELECT `gf_order_detail`.id_order
FROM `gf_order_detail`;
JOIN `gf_orders`
WHERE `gf_orders`.current_state=4;

But this query is far away from working.

Thank you :)

CodePudding user response：

I see basic syntax errors here:

INSERT INTO `gf_compta` (ID)
SELECT g.id_order
FROM `gf_order_detail` god
JOIN `gf_orders` g on g.<someColumn>=god.<somecolumn>
WHERE g.current_state=4;

UPDATED

INSERT INTO `gf_compta` (ID)
SELECT g.id_order
FROM `gf_orders` g
JOIN  `gf_order_detail` god on g.id_order=god.id_order
AND g.current_state=4;