SQL - return table if joins dont exist

Question

I basically want to return all uses if there distance is less than the radius which is fine. But if the meta data that is joined to the user doesn't exists, the result does not get returned.

Heres my query:

SELECT main.*  FROM
(
    SELECT
        users.*,
        gender.meta_value AS `gender`,
        sexual_orientation.meta_value AS `sexual_orientation`,
        relationship_status.meta_value AS `relationship_status`,
        interest_1.meta_value AS `interest_1`,
        interest_2.meta_value AS `interest_2`,
        interest_3.meta_value AS `interest_3`,
        interest_4.meta_value AS `interest_4`,
        interest_5.meta_value AS `interest_5`,
        interest_6.meta_value AS `interest_6`,
        address.address_line_1,
        address.address_line_2,
        address.town,
        address.county,
        address.postcode,
        address.country,
        address.longitude,
        address.latitude,
        ( 3959 * acos( cos( radians( {$usersLatitude} ) ) * cos( radians( address.latitude ) ) * cos( radians( address.longitude ) - radians( {$usersLongitude} ) ) + sin( radians( {$usersLatitude} ) ) * sin( radians( address.latitude ) ) ) ) AS distance
    FROM
        `users`
    JOIN
        `storage_varchars` AS `gender`
    ON
        gender.user_id = users.id AND gender.meta_name = 'gender'
    JOIN
        `storage_varchars` AS `sexual_orientation`
    ON
        sexual_orientation.user_id = users.id AND sexual_orientation.meta_name = 'sexual_orientation'
    JOIN
        `storage_varchars` AS `relationship_status`
    ON
        relationship_status.user_id = users.id AND relationship_status.meta_name = 'relationship_status'
    JOIN
        `storage_varchars` AS `interest_1`
    ON
        interest_1.user_id = users.id AND interest_1.meta_name = 'interest_1'
    JOIN
        `storage_varchars` AS `interest_2`
    ON
        interest_2.user_id = users.id AND interest_2.meta_name = 'interest_2'
    JOIN
        `storage_varchars` AS `interest_3`
    ON
        interest_3.user_id = users.id AND interest_3.meta_name = 'interest_3'
    JOIN
        `storage_varchars` AS `interest_4`
    ON
        interest_4.user_id = users.id AND interest_4.meta_name = 'interest_4'
    JOIN
        `storage_varchars` AS `interest_5`
    ON
        interest_5.user_id = users.id AND interest_5.meta_name = 'interest_5'
    JOIN
        `storage_varchars` AS `interest_6`
    ON
        interest_6.user_id = users.id AND interest_6.meta_name = 'interest_6'
    JOIN
        `payments` AS `address`
    ON
        address.user_id = users.id
) AS `main`
WHERE
    `main`.distance < {$radius}
ORDER BY
    `main`.distance

thanks in advance.

Drop the tables and start again? It's not a very efficient design. (BTW your query should already do what you state you are trying to achieve). And learn how to use the GIS indexing. — symcbean, Jan 15 '14 at 09:22
If you're still struggling, consider providing proper DDLs and/or an sqlfiddle TOGETHER WITH THE DESIRED RESULT SET — Strawberry, Jan 15 '14 at 09:49
this question perhaps may interest you: http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-join — AndreDurao, Jan 15 '14 at 10:07

Maksim · Accepted Answer · 2014-01-15T09:47:27.690

Here is what you need:

SELECT main.*  FROM
(
    SELECT
        users.*,
        gender.meta_value AS gender,
        sexual_orientation.meta_value AS sexual_orientation,
        relationship_status.meta_value AS relationship_status,
        interest_1.meta_value AS interest_1,
        interest_2.meta_value AS interest_2,
        interest_3.meta_value AS interest_3,
        interest_4.meta_value AS interest_4,
        interest_5.meta_value AS interest_5,
        interest_6.meta_value AS interest_6,
        address.address_line_1,
        address.address_line_2,
        address.town,
        address.county,
        address.postcode,
        address.country,
        address.longitude,
        address.latitude,
        ( 3959 * acos( cos( radians( {$usersLatitude} ) ) * cos( radians( address.latitude ) ) * cos( radians( address.longitude ) - radians( {$usersLongitude} ) ) + sin( radians( {$usersLatitude} ) ) * sin( radians( address.latitude ) ) ) ) AS distance
    FROM
        users
    LEFT JOIN
        storage_varchars AS gender
    ON
        users.id = gender.user_id AND gender.meta_name = 'gender'
    LEFT JOIN
        storage_varchars AS sexual_orientation
    ON
        users.id = sexual_orientation.user_id AND sexual_orientation.meta_name = 'sexual_orientation'
    LEFT JOIN
        storage_varchars AS relationship_status
    ON
        users.id = relationship_status.user_id AND relationship_status.meta_name = 'relationship_status'
    LEFT JOIN
        storage_varchars AS interest_1
    ON
        users.id = interest_1.user_id AND interest_1.meta_name = 'interest_1'
    LEFT JOIN
        storage_varchars AS interest_2
    ON
        users.id = interest_2.user_id AND interest_2.meta_name = 'interest_2'
    LEFT JOIN
        storage_varchars AS interest_3
    ON
        users.id = interest_3.user_id AND interest_3.meta_name = 'interest_3'
    LEFT JOIN
        storage_varchars AS interest_4
    ON
        users.id = interest_4.user_id AND interest_4.meta_name = 'interest_4'
    LEFT JOIN
        storage_varchars AS interest_5
    ON
        users.id = interest_5.user_id AND interest_5.meta_name = 'interest_5'
    LEFT JOIN
        storage_varchars AS interest_6
    ON
        users.id = interest_6.user_id AND interest_6.meta_name = 'interest_6'
    LEFT JOIN
        payments AS address
    ON
        users.id = address.user_id
) AS main
WHERE
    main.distance < {$radius}
ORDER BY
    main.distance;

SQL - return table if joins dont exist

1 Answers1