我强烈建议重写你的查询。删除子查询——它们不是必需的。然后使用 explicit joins 而不是逗号。

select c.id, c.name, c.address, c.city, c.phone  
from customer c
    join reservations r on c.id = r.customer_id
where r.rent_time = 'weekend'
group by c.id, c.name, c.address, c.city, c.phone  
having count(*) > 1

子查询 SELECT customer.ID, COUNT(*) AS amount_reservations 应该只有一列，而不是两列。
更改为：

SELECT name, address, city, phone  
  FROM customer  
  WHERE customer.ID IN (
    SELECT customer.ID
      FROM customer, (
        SELECT ID FROM customer, reservations 
          WHERE rent_time = 'weekend' AND ID = customer_ID
        ) AS foo 
      WHERE customer.ID = foo.ID 
      GROUP BY customer.ID HAVING count(*) > 1
    )

如果你想用 in ，您可以从根本上简化查询：

SELECT c.name, c.address, c.city, c.phone  
FROM customer c
WHERE c.ID IN (SELECT r.customer_id
               FROM reservations r
               WHERE r.rent_time = 'weekend' 
               GROUP BY r.customer_id
               HAVING COUNT(*) > 1
              ) ;

笔记：
不要在句子中使用逗号 FROM 条款。始终使用适当的、明确的、标准的 JOIN 语法。
对所有列引用使用表别名和限定列名。
你不需要一个 JOIN 在子查询中，因为您在reservations表中有customer id。