Query returns empty set

[Shaolin]

Hi Guys,

Please see my tables below:

http://img356.imageshack.us/img356/3794/tableswg5.png

I want to get all the records from fault table which are unallocated (thats all records for now). The problem is whenever I run my query, it should return the four records but it doesn't. See query below:

SELECT fault.*, fault_assignment.*
FROM fault
INNER JOIN fault_assignment ON fault_assignment.fault_assignment_id=fault.fault_id
WHERE status='Unallocated';
Any ideas on where I am going wrong ?



.

[skywalker2208]

You are joining the two table with the wrong columns. You are joining the fault_assignment_id with the fault_id when you need to join the two tables with the fault_id from both tables. So it should be

SELECT *
FROM fault f
JOIN fault_assignment fa ON fa.fault_id=f.fault_id
WHERE fa.status='Unallocated';