Restructuring SQL query based upon the table division-CodePudding

I had a working query that I framed this way.

SELECT b.name AS name, b.id AS userid, b.gender AS sex, b.dob AS dob, b.hide_age AS hideage, b.hide_distance AS hidedist, b.interests AS interests,
$distVal * DEGREES(ACOS(LEAST(1.0, COS(RADIANS(a.latitude)) * COS(RADIANS(b.latitude)) * COS(RADIANS(a.longitude) - RADIANS(b.longitude))   SIN(RADIANS(a.latitude))
* SIN(RADIANS(b.latitude))))) AS distance FROM users AS a
JOIN users AS b
WHERE b.id != :user AND a.id = :user AND b.country = :country AND FIND_IN_SET(b.gender, a.interested) AND YEAR(FROM_DAYS(DATEDIFF(CURRENT_DATE(), b.dob))) BETWEEN :fromage and :toage AND
b.id NOT IN (SELECT swp_to FROM swipes WHERE swp_from = a.id AND first_swp != 'rewind') AND
b.id NOT IN (SELECT swp_from FROM swipes WHERE swp_to = a.id AND second_swp NOT IN ('pending', 'rewind')) AND
a.latitude between b.latitude - 2 and b.latitude   2 and
a.longitude between b.longitude - 2 and b.longitude   2
HAVING distance < :maxdist ORDER BY RAND() LIMIT 30

In the above query the users table joins with itself. However, the fields in the users table were becoming too long so I created a new table called attributes and moved some fields from users table to attributes table. The fields are hide_age, hide_dist, latitude and longitude. Now, I am trying to re-frame the above query adding the newly created attributes table by joining it. However, that is not giving me the correct result.

What I tried:

SELECT CONCAT(b.first_name, ' ', b.last_name) AS name, b.id AS userid, b.gender AS sex, b.dob AS dob, d.hide_age AS hideage, d.hide_dist AS hidedist, b.interests AS interests,
$distVal * DEGREES(ACOS(LEAST(1.0, COS(RADIANS(c.latitude)) * COS(RADIANS(d.latitude)) * COS(RADIANS(c.longitude) - RADIANS(d.longitude))   SIN(RADIANS(c.latitude))
* SIN(RADIANS(d.latitude))))) AS distance FROM users AS a
LEFT JOIN attributes AS c ON a.id = c.user
JOIN users AS b
JOIN attributes AS d
WHERE b.id != :user AND d.user = :user AND a.id = :user AND b.country = :country AND FIND_IN_SET(b.gender, c.interested_in) AND YEAR(FROM_DAYS(DATEDIFF(CURRENT_DATE(), b.dob))) BETWEEN :fromage and :toage AND
b.id NOT IN (SELECT swp_to FROM swipes WHERE swp_from = a.id AND first_swp != 'rewind') AND
b.id NOT IN (SELECT swp_from FROM swipes WHERE swp_to = a.id AND second_swp NOT IN ('pending', 'rewind')) AND
c.latitude between d.latitude - 2 and d.latitude   2 and
c.longitude between d.longitude - 2 and d.longitude   2
HAVING distance < :maxdist ORDER BY RAND() LIMIT 30

How can I frame the 1st query correctly with the new attributes table? The id from the users table which is the user id is the user field in the attributes table to connect both the tables.

SAMPLE DATA

users table

id    name      gender
1     John      male
2     Shiva     male
3     Emily     female
4     Krishna   male

attributes table

id    user    latitude
1     1       10.25
2     2       10.25
3     3       10.25
4     4       10.25

This latitude field was first part of users table. Later moved to attributes table. The swipes table has nothing to do with attributes table so I am ignoring that here as a sample.

Current Result: Only returns two rows from user table 1 and 2 and it ignores 3 and 4.

Expected Result: Need all 4 records to show as it was being shown from the first query.

CodePudding user response：

Instead of doing multiple joins, define a view that joins the two tables.

CREATE VIEW users_and_attributes AS
SELECT u.id AS user_id, u.name, u.gender, ..., a.id AS attribute_id, a.hide_age, a.hide_dist, a.latitude, ...
FROM users AS u
JOIN attributes AS a ON a.user = u.id

Then you should be able to use the original query, just replacing users with the view name. Change:

FROM users AS a
JOIN users AS b

FROM users_and_attributes AS a
JOIN users_and_attributes AS b

Since the two tables both have duplicate column name id, you need to give aliases to those columns in the view, and refer to those aliases in the updated query. So a.id and b.id become a.user_id and b.user_id.