Thanks Dev_,

My situation is I don't know the value of both tables and there could be thousands of records in both tables. The above table is just for example.

Join the table Product and Material on ProductID. However, join the last row with if Status for each row is Pass. If the Status of any or all row is Fail, then join with the row that has Fail at first.
Can I have a query please?