Combining several database table together?

[eluser]SaSa[/eluser]
I have two tables `tour_foreign`&`tour_foreign_residence` in database and want merger this two table together that get output from PHP code as following example:

My tables and values they:

http://img4up.com/up2/44655891494644480084.gif
http://img4up.com/up2/74382499766550446741.gif

I want get as output `tour_foreign.id = tour_foreign_residence.relation` :

One-week tour of Istanbul_1 | 88888 & 99999 $ 112233 $ 445566 | Three nights and two days | 15:29<br>
One-week tour of Istanbul_2 | 55555 & 66666 $ 77777 $ 88888 | Three nights and two days | 12:03<br>
One-week tour of Istanbul_3 | 11111 & 22222 $ 33333 $ 44444 | Three nights and two days | 12:03<br>

My try is this but it don't give to me what that I want in above:

Code:

$this -> db -> query("
        SELECT
               @rownum := @rownum + 1 rownum,
               tour_foreign.id,
               tour_foreign.name,
               tour_foreign_residence.name_re,  
               tour_foreign.term,
               tour_foreign.time_go,
               tour_foreign.time_back,
               tour_foreign.type_of_vehicle
        FROM   tour_foreign
               INNER JOIN tour_foreign_residence
                 ON ( tour_foreign.id = tour_foreign_residence.relation )
               JOIN (SELECT @rownum := 0) r
        WHERE  tour_foreign.name LIKE "%' . $find . '%"
                OR tour_foreign_residence.name_re LIKE "%' . $find . '%"    
    ")

How can fix it?