将几个数据库表组合在一起? [英] Combining several database table together?
问题描述
我在数据库中有两个表 tour_foreign
& tour_foreign_residence
,并且希望合并这两个表, PHP代码如下示例:
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:
我的表和值it:
我想获取作为输出 tour_foreign.id = tour_foreign_residence.relation
:
一周的Istanbul_1 | 88888& 99999 $ 112233 $ 445566 |三晚两天| 15:29
一周的Istanbul_2 | 55555& 66666 $ 77777 |三晚两天| 12:03
一周的Istanbul_3 | 11111& 22222 $ 33333 $ 44444 |三晚两天| 12:03
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
我的尝试是这样,但它不会给我上面所需的东西:
My try is this but it don't give to me what that I want in above:
$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 . '%"
")
如何解决?
推荐答案
不知道你为什么加入@rownum,它可能是搞乱你的结果集。尝试取出它,看看它是否工作。
Not sure why you are joining on @rownum, it might be messing with your result set. Try taking it out and see if it works.
您的内部连接看起来确定,除非我从来没有看到它与封装的括号,但我怀疑它会工作正常。要测试你的查询,你可以删除复杂的where子句,只是放置在类似tour_foreign.id = 1的地方。
Your inner join looks ok, except I have never seen it with the wrapped parens, but I suspect it will work as expected. To test your query you could remove the complicated where clause and just put something like where tour_foreign.id = 1.
有点像:
SELECT
tf.*
FROM
tour_foreign AS tf
INNER JOIN
tour_foreign_residence AS tfr
ON
tfr.relation = tf.id
WHERE
tf.id = 1
我为您的较长表名(tf和tfr)实现了别名,因为它们更容易使用。
I implemented aliases for your longer table names (tf, and tfr) as they are easier to work with.
这篇关于将几个数据库表组合在一起?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!