[SOLVED] query problem...

[rsammy]

i have this query...

 

SELECT DISTINCT appt_pat_id, appt_loc as admit_visit_loc, '' as admit_room_no
FROM appt_schd
WHERE admit_visit_loc='Good Sam' AND appt_date ='2007-06-29' 
UNION
SELECT admit_pat_id as appt_pat_id, admit_visit_loc, admit_room_no
FROM admit_stat
WHERE admit_visit_loc = 'Good Sam'

 

whats wrong with this code? when i try executing this, it says 'unknown field admit_visit_loc in 'where clause'. this, it says for the first query. if i change that field name to appt_loc, it seems to be working but, it does not return all the records.

[teng84]

you cant use the alias on the condition always use the real field name

 

hope that helps

[rsammy]

the query, when i test it in MYSQLFront(a database interface) works fine - it results 11 results(one from appt_schd table for the day and 10 from admit_stat table). but, when i run this query in PHP, it returns just 2 records(from admit_stat table). any reason or idea why it behaves different here?