sql - Joining two tables on mysql -

i have 2 tables.

first table represented below includes count of dialled call hour wise number

+-------------+---------------+-------+ | card_no     | dialled_count | hour  | +-------------+---------------+-------+ | 9913528756  |   9           |  10   | | 9913528756  |   2           |  11   | | 9913528756  |   10          |  23   | +-------------+---------------+-------+ 

and other table represents count of message sent hourwise.

+-------------+---------------+-------+ | card_no     | message_count |  hour | +-------------+---------------+-------+ | 9913528756  |   5           |  6    | | 9913528756  |   10          |  10   | | 9913528756  |   12          |  17   | +-------------+---------------+-------+ 

i want join these tables expected final result :

+-------------+---------------+-------+--------------+ | card_no     | dialled_count |  hour | message_count| +-------------+---------------+-------+--------------+ | 9913528756  |   0           |  6    |   5          | | 9913528756  |   9           |  10   |   10         | | 9913528756  |   2           |  11   |   0          | | 9913528756  |   0           |  17   |   12         | | 9913528756  |   10          |  23   |   0          | +-------------+---------------+-------+--------------+ 

any helps appreciated

thanks in advance...

i think might want use union all rather.

somethign like

select card_no, dialled_count, hour, 0 message_count tblcall union select card_no, 0 dialled_count, hour, message_count tblmessage 

in response op question, change query like

select  dl.card_no,         dl.hour,         ifnull(c.dialled_count,0) dialled_count ,         ifnull(m.message_count,0) message_count    (             select  distinct                     card_no,                     hour                tblcall             union              select  distinct                     card_no,                     hour                tblmessage         ) dl left join         tblcall c   on  dl.card_no = c.card_no                     , dl.hour = c.hour left join         tblmessage m    on  dl.card_no = m.card_no                         , dl.hour = m.hour