Left Join Where problem

[will35010]

I have a query that works fine except it won't let me limit the results.

 

Current Query:

SELECT rooms.room, visit_data.patientid, visit_data.priority, visit_data.alert1, 
visit_data.alert2, visit_data.alert3, visit_data.areaid, visit_data.doctor, visit_data.nurse, visit_data.cc,
visit_data.current_status, visit_data.reg_time FROM rooms LEFT JOIN visit_data ON rooms.room = visit_data.room AND visit_data.areaid = 'ED'

 

Both tables have an areaid (example: rooms.areaid, visit_data.areaid)

 

I only want to pull data for a specific areaid, but no matter what areaid I put in it returns the same results with all areas like the AND clause isn't even there. How do I fix my query? I tried changing and to WHERE, but then it didn't return all the results I wanted from the left table. Thanks!

[mikosiko]

did you try:

....
        FROM rooms 
           LEFT JOIN visit_data ON rooms.room = visit_data.room AND visit_data.areaid = 'ED'
        WHERE rooms.areaid = 'ED'

[will35010]

did you try:

....
        FROM rooms 
           LEFT JOIN visit_data ON rooms.room = visit_data.room AND visit_data.areaid = 'ED'
        WHERE rooms.areaid = 'ED'

 

That worked perfectly. Thank you!!!