title: Combining two table with sql JOIN?
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/7814244/combining-several-database-table-together">Combining several database table together?</a>  </p>
  </blockquote>
  
  
  
  <p>I want from two database table get output like this:</p>
  
  <blockquote>
    <p>One-week tour of Istanbul_1   |   88888 &amp; 99999 $ 112233 $ 445566   |   Three nights and two days   |   15:29<br>
    One-week tour of Istanbul_2   |   55555 &amp; 66666 $ 77777   |   Three nights and two days   |   12:03<br>
    One-week tour of Istanbul_3   |   11111 &amp; 22222 $ 33333 $ 44444   |   Three nights and two days   |   12:03<br></p>
  </blockquote>
  
  <p>These are my tables:</p>
  
  <p><img src="https://i.stack.imgur.com/4T9Uq.gif" alt="enter image description here">
  <img src="https://i.stack.imgur.com/2t4KN.gif" alt="enter image description here"></p>
  
  <p><strong>Update:</strong></p>
  
  <p>it give me this output: <a href="http://img708.imageshack.us/img708/2404/outputnz.gif" rel="nofollow noreferrer">http://img708.imageshack.us/img708/2404/outputnz.gif</a> but if i put in $find value "55555" for where output is as: <a href="http://img528.imageshack.us/img528/3576/outputnow.gif" rel="nofollow noreferrer">http://img528.imageshack.us/img528/3576/outputnow.gif</a> but i want out is as: <a href="http://img832.imageshack.us/img832/120/outputwant.gif" rel="nofollow noreferrer">http://img832.imageshack.us/img832/120/outputwant.gif</a> . How is it?</p>
  
  <pre><code>$query = $this -&gt; db -&gt; query('
      SELECT
         @rownum := @rownum + 1 rownum,
         tour_foreign.id, 
         tour_foreign.name, 
         MIN(tour_foreign_residence.name_re) AS name_re, 
         tour_foreign.service, 
         tour_foreign.date_go, 
         tour_foreign.date_back, 
         tour_foreign.term,
         tour_foreign.useradmin_submit,
         tour_foreign.date_submit,
         GROUP_CONCAT( tour_foreign_residence.name_re 
                       ORDER BY tour_foreign_residence.name_re 
                       SEPARATOR " $ "
                     ) AS name_re_all
      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_all LIKE "%' . $find . '%"
      GROUP BY  tour_foreign.id ');
  </code></pre>
  
  <p>I get from above sql following error:</p>
  
  <blockquote>
    <p>A Database Error Occurred<br> Error Number: 1054<br></p>
    
    <p>Unknown column 'tour_foreign_residence.name_re_all' in 'where
    clause'<br></p>
    
    <p>SELECT @rownum := @rownum + 1 rownum, tour_foreign.id,
    tour_foreign.name, MIN(tour_foreign_residence.name_re) AS name_re,
    tour_foreign.service, tour_foreign.date_go, tour_foreign.date_back,
    tour_foreign.term, tour_foreign.useradmin_submit,
    tour_foreign.date_submit, GROUP_CONCAT( tour_foreign_residence.name_re
    ORDER BY tour_foreign_residence.name_re SEPARATOR " $ " ) AS
    name_re_all 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 "%%" OR
    tour_foreign_residence.name_re_all LIKE "%%" GROUP BY
    tour_foreign.id<br></p>
    
    <p>Filename:
    D:\xampp\htdocs\system\database\DB_driver.php<br></p>
    
    <p>Line Number: 330<br></p>
  </blockquote>