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

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