mySQL列表配置文件和电影在一个查询中使用复杂连接但有三个表

问题描述：

Overview: I want to get a list of profiles and the movies they like. All the output to be shown on one page. I am using mySQL and PHP, at moment running on localhost. Ideally i want this to be in one query, for speed.

Database table overview in mySQL as follow:

profile_tbl:
> id
> username
> about me
> gender
> hobbies
> profile_pic
> last_login


movies_tbl:
> id
> movie_name
> genre
> length
> rating
> description


profile_movies_rel_tbl
> id
> movie_id
> profile_id

Output:

I want to show 10 profiles and list of the all the movies they like. I was thinking following query:

SELECT  profile_tbl.*, movies_tbl.* FROM profile_tbl
LEFT JOIN profile_movies_rel_tbl
ON profile_movies_rel_tbl.movie_id = movies_tbl.id
LEFT JOIN profile_tbl
ON profile_tbl.id= profile_movies_rel_tbl.profile_id LIMIT 10

I also have a second issue, where I want to lists all the profile that have selected movie has favorite, again the page should list profiles and movies together. In this case, i was thinking of using the above query and adding following:

WHERE profile_movies_rel_tbl.movie_id = 4

Any one need more info, please leave comment.

thanks

概述： strong> 我想获得他们喜欢的个人资料和电影列表。所有输出都显示在一页上。我正在使用mySQL和PHP，现在运行在localhost上。理想情况下，我希望将其放在一个查询中，以提高速度。 p>

mySQL中的数据库表概述如下： p>

profile_tbl：＆gt; ; ID ＆GT; 用户名\ N'GT; 关于我＆gt; 性别＆GT; 爱好＆GT; profile_pic ＆GT; LAST_LOGIN movies_tbl：＆GT; ID ＆GT; MOVIE_NAME ＆GT; 流派＆GT; 长度＆GT; 评级＆GT; 描述 profile_movies_rel_tbl ＆GT; ID ＆GT; movie_id ＆GT; profile_id code> pre> 输出： strong> p> 我想显示10个配置文件和所有列表他们喜欢的电影。我在考虑以下查询： p> SELECT profile_tbl。*，movies_tbl。* FROM profile_tbl LEFT JOIN profile_movies_rel_tbl ON profile_movies_rel_tbl.movie_id = movies_tbl.id LEFT JOIN profile_tbl ON profile_tbl .id = profile_movies_rel_tbl.profile_id LIMIT 10 code> pre> 我还有第二个问题 strong>，我要列出已选择的所有个人资料电影很受欢迎，页面应该再次列出个人资料和电影。在这种情况下，我正在考虑使用上述查询并添加以下内容： p> WHERE profile_movies_rel_tbl.movie_id = 4 code> pre> 任何人都需要更多信息，请留下评论。 p> 谢谢 p> div>

答

I think you want to use LIMIT in a subquery to return 10 distinct profiles:

SELECT  profile_tbl.*, movies_tbl.* 
FROM (
     SELECT DISTINCT Id FROM profile_tbl LIMIT 10
    ) LimitTable
    INNER JOIN profile_tbl ON profile_tbl.Id=LimitTable.Id
    LEFT JOIN profile_movies_rel_tbl
        ON profile_movies_rel_tbl.profile_id = profile_tbl.id
    LEFT JOIN movies_tbl
        ON profile_movies_rel_tbl.movie_id = movies_tbl.id

This could return more than 10 rows, but it should only contain 10 profiles.

If you are wanting to return 10 rows, then look into using GROUP_CONCAT to combine your movies into a single column.

答

You can simple use joins for this. And using GROUP_CONCAT will give you a list of all movies but comma seperated. Which you can explode using php explode function whichi will give you the result in an array. Than you can use loop that array for displaying movie names

SELECT 
    pt.*,
    GROUP_CONCAT(movie_name) AS `Movies`
FROM profile_tbl AS pt
INNER JOIN profile_movies_rel_tbl AS pmrt ON pmrt.profile_id = pt.id
INNER JOIN movies_tbl AS mt ON mt.id = pmrt.movie_id
GROUP BY pt.id
    LIMIT 10
    ORDER BY pt.id DESC

mySQL列表配置文件和电影在一个查询中使用复杂连接但有三个表

相关推荐