php - MySQL 选择 IF INNER JOIN

Question

我有 3 个表:

place (placeId,placeName,...)
event (eventId, eventName,...)
picture (pictureId,associateId,picturePath,type [1 place 2 event])

这就是我想要的:

当图片类型为1时picture.associateId=place.placeId返回路径

当图片类型为2 picture.associateId=event.eventId返回路径

1 个地方 -> 许多事件

1个地方->一张图片

1 个事件 -> 一张照片

可能是这样的(请帮忙):

SELECT Place.placeName, Picture.picturePath, Event.eventId, ... FROM Place INNER JOIN Event ON Place.placeId = Event.eventPlace INNER JOIN Picture ON (IF Picture.type=1 return Picture.picturePath ELSE Picture.type=2 return Picture.picturePath)

此咨询返回一个 json 以根据日期在页面上显示事件:

SELECT Place.placeName, Place.placePopularity, Picture.picturePath, Event.eventId, Event.eventCount
FROM Place INNER JOIN Picture ON Place.placeId = Picture.associateId 
    INNER JOIN Event ON Place.placeId = Event.eventPlace
WHERE Picture.type = 2 AND Event.eventDate = '$date'
ORDER BY Event.eventCount DESC

Answer 1

用这个运行if else

    if () { // picture type 1
        // this is the query
        $query = "SELECT pictureTable.picture_path FROM placeTable INNER JOIN picture_table ON placeTable.placeId = picture_table.associateId";
        } else { // picture type 2 means for event

        $query = "SELECT pictureTable.picture_path FROM eventTable INNER JOIN  picture_table ON eventTable.eventId = picture_table.associateId";
       // now you can get the result
       }

希望这对您有所帮助。