CODEWITHSUNDEEP
mysqlsqljoinleft-joinouter-join
Roy Ryando
Roy Ryando

Reputation: 1288

MySQL Query LEFT OUTER JOIN AND IFNULL

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

Upvotes: 0

Views: 1292

Answers (4)

Pankaj_Dwivedi
Pankaj_Dwivedi

Reputation: 580

/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;

Upvotes: 2

Michael B
Michael B

Reputation: 129

   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`

Upvotes: 0

thecassion
thecassion

Reputation: 546

First let me try to understand what you want your query to do:

  1. The query must all st where st.st_id = at.at_id
  2. If st.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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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;

Upvotes: 1

Related Questions