If a subquery returns more than one row it needs to be compared via IN as = will throw an error.
SELECT * FROM form1 f1, form2 f2
WHERE f1.k8regNo IN ( SELECT f2.form2_ID FROM form2 f2, form4 f4
WHERE f2.form2_ID IN ( SELECT f4.form2_ID FROM form4 f4, form3 f3
However, subqueries are also an extremely inefficient way of doing this.
Also, re your last post, it is not poor practice not to specify the conditions of a join for readability sake (particularly if she is learning).
An updated version of my last example with all tables included:
FROM form1 t1
JOIN form2 t2 ON t1.k8regNo = t2.k8regNo
JOIN form4 t4 ON t2.form2_ID = t4.form2_ID
JOIN form3 t3 ON t4.k9regNo = t3.k9regNo
ORDER BY moveDt
This will return a dataset of all tables joined without filtering of any kind. Without knowing more about the data and the purpose of the query it is impossible for me to make recommendations about the types of joins to use, or other criterea (the resultant dataset may be quite large).
I tried the way @Hearth taught me, i do it like this
SELECT form1.k8regNo, form1.moveDt, form1.consignee, form2.goodsDesc, form2.k8goodsQty, form3.k9regNo, form3.outDt, form4.k9goodsQty
FROM form t1,
LEFT JOIN form2 t2 ON t1.k8regNo = t2.k8regNo
LEFT JOIN form4 t4 ON t2.k8_goodsID = t4.k8goodsID
LEFT JOIN form3 t3 ON t4.k9regNo = t3.k9regNo
WHERE form1.k8regNo = '25'
but it return me an error ,
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join form2 t2 ON t1.k8regNo = t2.k8regNo LEFT JOIN form4 t4 ON t' at line 1