PhishTail PhishTail - 2 months ago 5
SQL Question

SQL subquery not producing correct results

I'm using MySQL 5.7.15

I'm trying to get a list of all the business that match the where clause (near a certain location), for each business I need to get the picture with the most likes. Here is where it gets complicated, if the business has a plan type other than 1 then I need the picture with the most likes where most_liked_picture.business_picture's value is 1, otherwise I just need the pictures with the most likes. In an attempt to do this I created an outer query that will give me all the businesses and an inner query with sub queries to give me the picture with the most likes that satisfies the order by clause.

Two of the inner queries are not producing the desired results. It gives results for some of the rows but not all, which I find very strange. For now I have not tried to limit the results of the inner query to just one per outer query because I can't get the inner query sub's to produce the correct results.

When I break the sub queries out I get the correct results so I know the queries are good, it has to be the way I'm combining them?

I am self taught in SQL so if you see something that isn't standard or is weird please be gentle.

Here is the table structure and data:

businesses
+----+----------------------------+-----------+------------+----------------|
| id | name | lat | lng | point_location |
+----+----------------------------+-----------+------------+----------------|
| 1 | test_business_1 | 28.418908 | -81.586254 | POINT(lng,lat) |
| 2 | Sea_World | 32.764800 | 117.226600 | POINT(lng,lat) |
| 3 | Disneyland | 33.812100 | 117.919000 | POINT(lng,lat) |
| 4 | Disney World | 28.417839 | -81.581235 | POINT(lng,lat) |
| 5 | business near Disney World | 28.408642 | -81.572607 | POINT(lng,lat) |
+----+----------------------------+-----------+------------+----------------|

business_plans
+----+--------------+-------------+---------+------------+------------+
| id | plan_type_id | business_id | user_id | start_date | end_date |
+----+--------------+-------------+---------+------------+------------+
| 1 | 1 | 1 | 1 | 2015-01-01 | 2015-12-31 |
| 2 | 2 | 1 | 2 | 2016-01-01 | 2016-12-31 |
| 3 | 2 | 2 | 2 | 2016-01-01 | 2016-12-31 |
| 4 | 1 | 3 | 1 | 2016-01-01 | 2016-12-31 |
| 5 | 2 | 4 | 1 | 2016-01-01 | 0000-00-00 |
+----+--------------+-------------+---------+------------+------------+

pictures
+----+-------------+---------+---------------------------------------+---------------------------------------+-----------+------------+---------------------------+----------------+-------------------+-------------+----------------+------------------+-----------------------+---------------+---------------------+---------------------+---------------------+---------------------+
| id | business_id | user_id | image_path | title | lat | lng | point_location | lifetime_likes | lifetime_dislikes | month_likes | month_dislikes | business_picture | business_main_picture | business_icon | effective_date | expire_date | updated_at | created_at |
+----+-------------+---------+---------------------------------------+---------------------------------------+-----------+------------+---------------------------+----------------+-------------------+-------------+----------------+------------------+-----------------------+---------------+---------------------+---------------------+---------------------+---------------------+
| 1 | 2 | 1 | sea world image_path_1 | sea world logo | 32.764800 | 117.226600 | POINT(lng,lat) | 5 | 5 | 5 | 5 | 1 | 0 | 1 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 2 | 3 | 0 | disney_image_path_2 | disney main picture | 33.812100 | 117.919000 | POINT(lng,lat) | 1 | 0 | 1 | 0 | 1 | 1 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 3 | 3 | 2 | disney user uploaded pic | NULL | 33.812100 | 117.919000 | POINT(lng,lat) | 5 | 5 | 5 | 5 | 0 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 4 | 3 | 0 | disney expired pic | disney expired pic | 33.812100 | 117.919000 | POINT(lng,lat) | 20 | 0 | 20 | 0 | 1 | 0 | 0 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 5 | 3 | 2 | disney_highest_points | disney highest points | 33.812100 | 117.919000 | POINT(lng,lat) | 10 | 2 | 10 | 2 | 1 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-01-01 00:00:00 | 2016-01-01 00:00:00 |
| 6 | 4 | 1 | disneyworld_highest_business_pic | disneyworld_highest_business_pic | 28.417839 | -81.581235 | POINT(lng,lat) | 20 | 1 | 20 | 1 | 1 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-09-22 22:40:50 | 2016-01-01 00:00:00 |
| 7 | 4 | 1 | disneyworld_highest_user_point_pic | disneyworld_highest_user_point_pic | 28.417839 | -81.581235 | POINT(lng,lat) | 45 | 1 | 45 | 1 | 0 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-09-22 22:36:46 | 2016-01-01 00:00:00 |
| 8 | 5 | 2 | near_disneyworld_highest_business_pic | near_disneyworld_highest_business_pic | 28.417839 | -81.581235 | POINT(lng,lat) | 20 | 1 | 20 | 1 | 1 | 0 | 0 | 2016-01-01 00:00:00 | 2016-12-31 00:00:00 | 2016-09-23 00:08:20 | 2016-01-01 00:00:00 |
+----+-------------+---------+---------------------------------------+---------------------------------------+-----------+------------+---------------------------+----------------+-------------------+-------------+----------------+------------------+-----------------------+---------------+---------------------+---------------------+---------------------+---------------------+


users
+----+-----------+--------------------+
| id | username | picture_path |
+----+-----------+--------------------+
| 1 | test | user1_picture_Path |
| 2 | username2 | |
| 3 | username3 | |
| 4 | username5 | |
| 5 | username5 | |
| 6 | username6 | |
| 7 | username7 | NULL |
+----+-----------+--------------------+


user_picture_swipe
+----+------------+-------------+---------+-------+
| id | picture_id | business_id | user_id | liked |
+----+------------+-------------+---------+-------+
| 1 | 1 | 2 | 2 | 0 |
| 2 | 2 | 3 | 2 | 1 |
| 3 | 2 | 3 | 1 | 1 |
| 4 | 3 | 3 | 1 | 1 |
| 5 | 4 | 3 | 1 | 0 |
| 6 | 7 | 4 | 1 | 1 |
| 7 | 6 | 4 | 1 | 0 |
| 9 | 8 | 5 | 2 | 1 |
+----+------------+-------------+---------+-------+


Here is the query:

SELECT businesses.id AS business_id, businesses.name AS business_name,
most_liked_picture.business_plan_type_id, most_liked_picture.picture_id,
businesses.lat, businesses.lng, most_liked_picture.image_path,
most_liked_picture.picture_title, most_liked_picture.lifetime_likes,
most_liked_picture.business_picture,
ST_Distance_Sphere(businesses.point_location, POINT(-81.581235, 28.417839)) AS
distance_from_user,
most_liked_picture.uploaded_username,
most_liked_picture.uploaded_user_image_path,
most_liked_picture.user_liked_picture
FROM businesses LEFT JOIN
(SELECT most_liked_picture.id AS picture_id, businesses.id AS business_id,
businesses.name AS business_name,
current_business_plan.plan_type_id AS business_plan_type_id,
businesses.lat, businesses.lng, most_liked_picture.image_path,
title AS picture_title, most_liked_picture.lifetime_likes,
business_picture,
ST_Distance_Sphere(businesses.point_location, POINT(-81.581235,
28.417839)) AS distance_from_user,
CASE business_picture
WHEN 0 THEN user_uploaded_picture.username
ELSE ""
END AS uploaded_username,
CASE business_picture
WHEN 0 THEN user_uploaded_picture.picture_path
ELSE ""
END AS uploaded_user_image_path,
IFNULL(current_user_liked_picture, NULL) AS user_liked_picture
FROM users AS user_uploaded_picture RIGHT JOIN (
(SELECT business_id, plan_type_id
FROM business_plans
WHERE (CURRENT_DATE() BETWEEN start_date AND end_date) OR
((end_date IS NULL) AND (CURRENT_DATE >= start_date))
) AS current_business_plan
RIGHT JOIN (businesses LEFT JOIN (
(SELECT picture_id, liked AS current_user_liked_picture
FROM user_picture_swipes
WHERE (user_id = 1)
) AS user_picture_swipe
RIGHT JOIN pictures AS most_liked_picture
ON user_picture_swipe.picture_id = most_liked_picture.id)
ON businesses.id = most_liked_picture.business_id)
ON current_business_plan.business_id = businesses.id)
ON user_uploaded_picture.id = most_liked_picture.user_id
WHERE ST_Within(businesses.point_location,
envelope(linestring(POINT(-81.581235 - 5 /
abs(cos(radians(28.417839)) * 69),28.417839 - (5 / 69)),
POINT(-81.581235 + 5 / abs(cos(radians(28.417839)) * 69),28.417839 +
(5 / 69)))))
ORDER BY
IF(IFNULL(current_business_plan.plan_type_id, 1) != 1,
IFNULL(most_liked_picture.business_picture, 0), 0) DESC,
lifetime_likes DESC
) AS most_liked_picture ON businesses.id = most_liked_picture.business_id
WHERE ST_Within(businesses.point_location, envelope(linestring(POINT(-81.581235
- 5 / abs(cos(radians(28.417839)) * 69),28.417839 - (5 / 69)),POINT(-81.581235
+ 5 / abs(cos(radians(28.417839)) * 69),28.417839 + (5 / 69)))))
ORDER BY ST_Distance_Sphere(businesses.point_location, POINT(-81.581235,
28.417839));


Here are the results I'm getting (shortened to fit page):

+-------------+--------------+--------------+-------+----------+---------------+
| business_id | name | plan_type_id | likes | username | liked_picture |
+-------------+--------------+--------------+-------+----------+---------------+
| 4 | Disney World | NULL | 20 | | 0 |
| 4 | Disney World | NULL | 45 | test | 1 |
| 1 | test_business| 2 | | NULL | NULL |
| 5 | business near| NULL | 20 | | NULL |
+-------------+--------------+--------------+-------+----------+---------------+


The results I should be getting:

+-------------+--------------+--------------+-------+----------+---------------+
| business_id | name | plan_type_id | likes | username | liked_picture |
+-------------+--------------+--------------+-------+----------+---------------+
| 4 | Disney World | 2 | 20 | test | 0 |
| 4 | Disney World | 2 | 45 | test | 1 |
| 1 | test_business| 2 | | NULL | NULL |
| 5 | business near| NULL | 20 | username2| 1 |
+-------------+--------------+--------------+-------+----------+---------------+


Sorry this post is so long. Any help is appreciated.

******************************* update for query *******************************

I finally figured it out! The outer query was the culprit, once it was removed I got the desired results. Here is the final query (using no right joins and no blocks in the from). Thanks to Used_By_Already for pointing me in the right direction.

SELECT businesses.id AS business_id, businesses.name AS business_name,
current_business_plan.plan_type_id AS business_plan_type_id, businesses.lat, businesses.lng,
most_liked_picture.id AS picture_id, most_liked_picture.image_path, title AS picture_title,
most_liked_picture.lifetime_likes, business_picture, picture_uploaded_user.username AS uploaded_username,
picture_uploaded_user.picture_path AS uploaded_user_image_path,
user_picture_swipe.current_user_liked_picture AS user_liked_picture,
ST_Distance_Sphere(businesses.point_location, POINT(-81.581235, 28.417839)) AS distance_from_user

FROM
(SELECT business_id, plan_type_id
FROM business_plans
WHERE (CURRENT_DATE() BETWEEN start_date AND end_date) OR ((end_date IS NULL) AND
(CURRENT_DATE >= start_date))) AS current_business_plan
INNER JOIN businesses ON current_business_plan.business_id = businesses.id
LEFT JOIN pictures AS most_liked_picture ON businesses.id = most_liked_picture.business_id
LEFT JOIN users AS picture_uploaded_user ON most_liked_picture.user_id = picture_uploaded_user.id
LEFT JOIN
(SELECT picture_id, liked AS current_user_liked_picture
FROM user_picture_swipes
WHERE (user_id = 1)) AS user_picture_swipe ON most_liked_picture.id = user_picture_swipe.picture_id

WHERE ((expire_date IS NULL) OR (CURRENT_DATE() <= expire_date)) AND
ST_Within(businesses.point_location, envelope(linestring(POINT(-81.581235 - 5 / abs(cos(radians(28.417839)) * 69),28.417839 - (5 / 69)), POINT(-81.581235 + 5 / abs(cos(radians(28.417839)) * 69),28.417839 + (5 / 69)))))

ORDER BY
IF(IFNULL(current_business_plan.plan_type_id, 1) != 1, IFNULL(most_liked_picture.business_picture, 0), 0) DESC,
most_liked_picture.lifetime_likes DESC,
ST_Distance_Sphere(businesses.point_location, POINT(-81.581235, 28.417839))


Now if I could just figure out how to only return 1 picture for each business. I tried using an aggregate MAX(most_liked_picture.lifetime_likes) and GROUP BY businesses.id however it removes my order by so I don't get the correct picture.

Answer

This simple query:

SELECT
      b.*
    , bp.business_id
    , bp.plan_type_id
    , p.likes
    , p.b_pic
    , u.username
    , u.picture_path
FROM business_plans AS bp
INNER JOIN businesses AS b ON bp.business_id = b.id
left join pictures AS p on b.id = p.id
left join users AS u on p.user_id = u.id
WHERE (CURRENT_DATE() BETWEEN bp.start_date AND bp.end_date)
     OR 
      (bp.end_date IS NULL AND CURRENT_DATE() >= bp.start_date)

produces a result like this:

id name            lat lng point_location business_id plan_type_id likes b_pic username  picture_path       
-- --------------- --- --- -------------- ----------- ------------ ----- ----- --------- ------------------ 
1  test_business_1 28  -82 null           1           1            5     1     test      user1_picture_Path 
1  test_business_1 28  -82 null           1           2            5     1     test      user1_picture_Path 
2  Sea_World       33  117 null           2           2            1     1     null      null               
3  Disneyland      34  118 null           3           1            5     0     username2 null               
4  Disney World    28  -82 null           4           2            20    1     null      null               

(5 row(s) returned)

(25 row(s) affected)

I suggest you restart from scratch.

Perhaps this is the starting point? But truly I am not sure

SELECT b.*, bp.*
FROM business_plans AS bp
INNER JOIN businesses AS b on bp.business_id = b.id

-- sample data needs start_date or end_date

WHERE (CURRENT_DATE() BETWEEN start_date AND end_date) 
OR (end_date IS NULL AND CURRENT_DATE >= start_date)

Also make sure your test data will satisfy the where clauses

CREATE TABLE businesses
    (`id` int, `name` varchar(26), `lat` numeric, `lng` numeric, `point_location` int)
;

INSERT INTO businesses
    (`id`, `name`, `lat`, `lng`, `point_location`)
VALUES
    (1, 'test_business_1', 28.418908, -81.586254, NULL),
    (2, 'Sea_World', 32.764800, 117.226600, NULL),
    (3, 'Disneyland', 33.812100, 117.919000, NULL),
    (4, 'Disney World', 28.417839, -81.581235, NULL),
    (5, 'business near Disney World', 28.408642, -81.572607, NULL)
;


CREATE TABLE business_plans
    (`id` int, `plan_type_id` int, `business_id` int)
;

INSERT INTO business_plans
    (`id`, `plan_type_id`, `business_id`)
VALUES
    (1, 1, 1),
    (2, 2, 1),
    (3, 2, 2),
    (4, 1, 3),
    (5, 2, 4)
;


CREATE TABLE pictures
    (`id` int, `business_id` int, `user_id` int, `lat` int, `lng` int, `point` int, `likes` int, `b_pic` int)
;

INSERT INTO pictures
    (`id`, `business_id`, `user_id`, `lat`, `lng`, `point`, `likes`, `b_pic`)
VALUES
    (1, 2, 1, 32.764800, 117.226600, NULL, 5, 1),
    (2, 3, 0, 33.812100, 117.919000, NULL, 1, 1),
    (3, 3, 2, 33.812100, 117.919000, NULL, 5, 0),
    (4, 3, 0, 33.812100, 117.919000, NULL, 20, 1),
    (5, 3, 2, 33.812100, 117.919000, NULL, 10, 1),
    (6, 4, 1, 28.417839, -81.581235, NULL, 20, 1),
    (7, 4, 1, 28.417839, -81.581235, NULL, 45, 0),
    (8, 5, 2, 28.417839, -81.581235, NULL, 20, 1)
;


CREATE TABLE users
    (`id` int, `username` varchar(9), `picture_path` varchar(18))
;

INSERT INTO users
    (`id`, `username`, `picture_path`)
VALUES
    (1, 'test', 'user1_picture_Path'),
    (2, 'username2', NULL),
    (3, 'username3', NULL),
    (4, 'username5', NULL),
    (5, 'username5', NULL),
    (6, 'username6', NULL),
    (7, 'username7', NULL)
;


CREATE TABLE user_picture_swipe
    (`id` int, `picture_id` int, `business_id` int, `user_id` int, `liked` int)
;

INSERT INTO user_picture_swipe
    (`id`, `picture_id`, `business_id`, `user_id`, `liked`)
VALUES
    (1, 1, 2, 2, 0),
    (2, 2, 3, 2, 1),
    (3, 2, 3, 1, 1),
    (4, 3, 3, 1, 1),
    (5, 4, 3, 1, 0),
    (6, 7, 4, 1, 1),
    (7, 6, 4, 1, 0),
    (9, 8, 5, 2, 1)
;
Comments