mysql多连接加连接表查询

mbskvtky 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(410)

我正在处理这个问题，有点挣扎。连接对我来说是有意义的，但是从 images 有一个称为 user_image 正在逃离我。

SELECT c.id, c.city, ROUND(( 3959 * acos( cos( radians(27.9861) ) * cos( radians( lat ) ) 

* cos( radians( lng ) - radians(-80.6628) ) + sin( radians(27.9861) ) * sin(radians(lat)) ) ),0) AS distance, t.id, t.name, t.price, t.duration, d.description, u.fname, i.image_path, i.image_name

FROM city c
    JOIN trip t ON c.id = t.city_id
    JOIN trip_description d ON t.id = d.trip_id
    JOIN user u ON t.user_id = u.id
    -- Need to get all images that match trip and is_main = 1
HAVING distance < 20 
ORDER BY distance 
LIMIT 0 , 45;

mysql union many-to-many

来源：https://stackoverflow.com/questions/51637959/mysql-query-with-multiple-joins-plus-junction-table

2条答案

按热度按时间

iyfjxgzm1#

好的，我想我明白了。感谢所有回应的人。

SELECT c.id, c.city, ROUND(( 3959 * acos( cos( radians(27.9861) ) * cos( radians( lat ) ) 

* cos( radians( lng ) - radians(-80.6628) ) + sin( radians(27.9861) ) * sin(radians(lat)) ) ),0) AS distance, t.id, t.name, t.price, t.duration, d.description, u.fname, i.path, i.name

FROM city c
    JOIN trip t ON c.id = t.city_id
    JOIN trip_description d ON t.id = d.trip_id
    JOIN user u ON t.user_id = u.id
    LEFT OUTER JOIN user_image ui ON ui.trip_id = t.id
    LEFT OUTER JOIN image i ON ui.image_id = i.id
    AND i.main=1
HAVING distance < 20 
ORDER BY distance 
LIMIT 0 , 45;

赞(0）回复(0）举报 2021-06-20

svujldwt2#

假设您正在查找给定用户的所有图像名称，则 is_main = 1 以及 is_active = 1 :

SELECT c.id as city_id, c.city, u.fname, t.name, d.description, i.image_name
FROM image i
    LEFT JOIN user_image ui ON ui.image_id = i.id 
    LEFT JOIN user u ON u.id = ui.user_id
    LEFT JOIN trip t ON t.id = ui.trip_id
    LEFT JOIN trip_description d ON t.id = d.trip_id
    LEFT JOIN city c ON c.id = t.city_id
WHERE u.id = 1
    AND i.is_main = 1
    AND i.active = 1
ORDER BY c.id;

赞(0）回复(0）举报 2021-06-20

我来回答

mysql多连接加连接表查询

2条答案

相关问题

热门标签

最新问答