结合两个表与sql JOIN？ [英] Combining two table with sql JOIN?

问题描述

可能重复：

，但如果我放在$ find值55555的输出为： http://img528.imageshack.us/img528/3576/outputnow.gif 但我想要的是： http://img832.imageshack.us/img832/120/outputwant.gif 。如何？

  $ query = $ this  - > db  - > 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'）; 
  

我从上面的sql获取错误：

< blockquote>

发生数据库错误
错误号码：1054

未知列'tour_foreign_residence.name_re_all'在'其中
子句'

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 （
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

文件名：
D：\xampp\htdocs\system\database\DB_driver.php

行号：330

解决方案

您需要按照某个组或聚合 GROUP_CONCAT code>将收集所有行到一个：

  GROUP BY tour_foreign.id 
  

使用：

  $ query = $这 - > db  - > query（'
 SELECT 
 @rownum：= @rownum + 1 rownum，
 tour_foreign.id，
 tour_foreign.name，
 MIN（tour_foreign_residence.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 LIKE％'。$ find。'％
 GROUP BY tour_foreign.id'）; 
  




Possible Duplicate:
Combining several database table together?

I want from two database table get output like this:

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

These are my tables:

Update:

it give me this output: http://img708.imageshack.us/img708/2404/outputnz.gif but if i put in $find value "55555" for where output is as: http://img528.imageshack.us/img528/3576/outputnow.gif but i want out is as: http://img832.imageshack.us/img832/120/outputwant.gif . How is it?

$query = $this -> db -> 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 ');

I get from above sql following error:

A Database Error Occurred
Error Number: 1054

Unknown column 'tour_foreign_residence.name_re_all' in 'where clause'

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

Filename: D:\xampp\htdocs\system\database\DB_driver.php

Line Number: 330

解决方案

You need to group by something or the aggregate GROUP_CONCAT() will collect all rows into one:

GROUP BY  tour_foreign.id 

Use:

$query = $this -> db -> 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 LIKE "%' . $find . '%"
    GROUP BY  tour_foreign.id ');

这篇关于结合两个表与sql JOIN？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！