doula2426
doula2426
2016-02-02 07:12

左外连接错误导致MYSQL

已采纳

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
        }
    ]
}
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongtanzhu5417 dongtanzhu5417 5年前

    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
    
    点赞 评论 复制链接分享
  • doutan1637 doutan1637 5年前

    If you want to show both users then use below query-

    SELECT a. * , b.property_id AS fav, group_concat(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'
    group by a.id
    UNION
    SELECT a. * , b.property_id AS fav, group_concat(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'
    group by a.id
    ORDER BY id DESC
    

    Note: If you don't want to show multiple comma seperated users then don't use group_concat function then your query will just give you first user.

    点赞 评论 复制链接分享

为你推荐