I have this query:
SELECT st.st_id, st.id, st.name, at.status, at.date
FROM st,at
WHERE st.st_id = at.at_id;
but I want the result return all of st table, if st.st_id exist in at.st_id, the status column is what in the at table, if not the status is 'H', anyone can help?
this is my table column
/Use IFNULL if DB is MYSQL/
SELECT st.st_id, st.id, st.name, IFNULL(at.status,'H') as status, at.date
FROM st LEFT JOIN
at
ON st.st_id = at.at_id;
Never use commas in the FROM clause. Always use proper, explicit JOIN syntax. Then it would be easier to write the query:
SELECT st.st_id, st.id, st.name, COALESCE(at.status, 'H') as status, at.date
FROM st LEFT JOIN
at
ON st.st_id = at.at_id;
First let me try to understand what you want your query to do:
st where st.st_id = at.at_idst.st_id = at.st_id then at.status = status else status='H'The script that I suggest:
SELECT st.st_id, st.id, st.name,
IF(st.st_id = at.st_id, at.status, 'H') AS status, at.date
FROM st LEFT JOIN at ON st.st_id = at.at_id;
SELECT st.st_id,
st.id,
st.name,
st.cl_id,
st.gender,
CASE WHEN ISNULL(at.st_id ) THEN 'H' ELSE at.status
FROM `st`
LEFT JOIN `at` ON `st`.`st_id` = `at`.`st_id`
sttable inattableat_idorst_id? I ask you this question because I don't understand why you sayWHERE st.st_id = at.at_id;