Hi SQL Gurus,
How do I join two tables with additional restrictions? Here is the scenario:
There is First Table that has two columnsProductID and ProductName.

ProductID ProductName
1 P1
2 P2

The second Table has 4 columns :
MaterialID ProductID MaterialName Status
M1                 1                       MName 1                         Pass
M2               1                      MName2                           Pass
M3              1                      MName3                            Pass

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?