左外连接错误导致MYSQL

Question

I am working with MySQL joins. I have two tables in which I need a join. First table contains all the real estate properties and second table contains the users who add favorite to that property. Now I want to show all the property with the favorite icon for the user who login. I write the below query in MySQL. But this query returns me all the record.

SELECT a. * , b.property_id AS fav, b.user_id
FROM `property_for_sale` a
LEFT OUTER JOIN `cpo_favourite_property` b ON a.id = b.property_id
WHERE a.property_type = 'Commercial'
UNION
SELECT a. * , b.property_id AS fav, b.user_id
FROM `property_for_rent` a
LEFT OUTER JOIN `cpo_favourite_property` b ON a.id = b.property_id
WHERE a.property_type = 'Commercial'
ORDER BY id DESC

There is one more problem with this. This query return me duplicate property if more than one user add the same property in favorite

Here is the final output I am getting from api when I use this query

{
    "success": "1",
    "user": [
        {
            "id": "1266",
            "date": "2016-01-25",
            "date_25_days": "2016-02-19",
            "date_30_days": "2016-02-24",
            "time": "04:47:40 PM",
            "user_name": "anil123", 
            "fileupload": "186_39151.jpg",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "gg",
            "fav": "1266",
            "user_id": "19"
        },
        {
            "id": "1266",
            "date": "2016-01-25",
            "date_25_days": "2016-02-19",
            "date_30_days": "2016-02-24",
            "time": "04:47:40 PM",
            "user_name": "anil123", 
            "fileupload": "186_39151.jpg",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "gg",
            "fav": "1266",
            "user_id": "480"
        },
        {
            "id": "1144",
            "date": "2015-12-07",
            "date_25_days": "2016-01-01",
            "date_30_days": "2016-01-06",
            "time": "05:45:30 PM",
            "user_name": "Realtyup Estate Agency", 
            "fileupload": "464_IMG-20140812-WA0063.jpg",
            "image2": "821_IMG-20140812-WA0064.jpg",
            "image3": "Null",
            "image4": "Null",
            "description": "Commercial showroom at Mansa Devi Complex Sector 4 Panchkula.Ground/basement/first/second fully constructed floors.Ample parking.Corner three side open.Well suited for any kind of business establishment",
            "fav": null,
            "user_id": null
        },
        {
            "id": "625",
            "date": "2016-02-01",
            "date_25_days": "2016-02-25",
            "date_30_days": "2016-03-01",
            "time": "02:25:40 AM",
            "user_name": "", 
            "fileupload": "Null",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "VjD7Gu http://www.FyLitCl7Pf7kjQdDUOLQOuaxTXbj5iNG.com",
            "fav": null,
            "user_id": null
        },
        {
            "id": "624",
            "date": "2016-01-31",
            "date_25_days": "2016-02-25",
            "date_30_days": "2016-03-01",
            "time": "05:44:10 PM",
            "user_name": "", 
            "fileupload": "Null",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "DIMgVX http://www.FyLitCl7Pf7kjQdDUOLQOuaxTXbj5iNG.com",
            "fav": null,
            "user_id": null
        },
        {
            "id": "623",
            "date": "2016-01-31",
            "date_25_days": "2016-02-25",
            "date_30_days": "2016-03-01",
            "time": "12:59:54 PM",
            "user_name": "", 
            "fileupload": "Null",
            "image2": "Null",
            "image3": "Null",
            "image4": "Null",
            "description": "2lx6j8 http://www.FyLitCl7Pf7kjQdDUOLQOuaxTXbj5iNG.com",
            "fav": null,
            "user_id": null
        }
    ]
}

Answer 1

I think you're missing restriction on currently logged user. Add it to the ON clause (:currentUserId placeholder):

SELECT a. * , b.property_id AS fav, b.user_id
FROM `property_for_sale` a
LEFT OUTER JOIN `cpo_favourite_property` b ON a.id = b.property_id AND b.user_id = :currentUserId
WHERE a.property_type = 'Commercial'
UNION
SELECT a. * , b.property_id AS fav, b.user_id
FROM `property_for_rent` a
LEFT OUTER JOIN `cpo_favourite_property` b ON a.id = b.property_id AND b.user_id = :currentUserId
WHERE a.property_type = 'Commercial'
ORDER BY id DESC