Delete join two tables even if the foreign key doesn't exist-CodePudding

The method below delete the room and messages in another table that have the same foreign key, but if the room doesn't have messages is not deleted. I'm using mysql8 and java 11.

public void deleteRoom() {
        // TODO Auto-generated method stub
        try {
            PreparedStatement preparedStatement = this.databaseMySQL.getConnection().
                    prepareStatement("DELETE OS_ROOMS, OS_ROOM_MESSAGES FROM OS_ROOMS INNER JOIN OS_ROOM_MESSAGES WHERE OS_ROOMS.ROS_ID = OS_ROOM_MESSAGES.ROM_ROS_ID AND OS_ROOMS.ROS_ID = ?");
            preparedStatement.setInt(1, RoomChatSession.id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

I searched for delete case,and this for example `

"DELETE OS_ROOMS, OS_ROOM_MESSAGES FROM OS_ROOMS INNER JOIN OS_ROOM_MESSAGES WHERE (OS_ROOMS.ROS_ID = OS_ROOM_MESSAGES.ROM_ROS_ID AND OS_ROOMS.ROS_ID = ?) OR (OS_ROOMS_ID = ?)"

` I want to delete the room even it doesn't have messages, it is possibile using only on sql command?

CodePudding user response：

Please check here the different methods of joining two tables in MySQL. Your sql statement performs an intersection while you just need to map messages to rooms. So instead of INNER JOIN you need to use LEFT JOIN and your query becomes:

DELETE OS_ROOMS, OS_ROOM_MESSAGES FROM OS_ROOMS LEFT JOIN OS_ROOM_MESSAGES WHERE OS_ROOMS.ROS_ID = OS_ROOM_MESSAGES.ROM_ROS_ID AND OS_ROOMS.ROS_ID = ?