根据另一个表中的条件计算表中的行

I have two tables, "Booking" and "Sports facility". A user can book a given sports facility in a given hour (each facility/hour has a unique ID, like for example "Swimming Pool" at "9:00" will have a different ID than at "10:00", and also a different one in a different day).

我有两张桌子，“预订”和“体育设施”。用户可以在给定的小时内预订给定的体育设施（每个设施/小时具有唯一的ID，例如“9:00”的“游泳池”将具有与“10:00”不同的ID，并且还在不同的一天不同的一个）。

Booking has the following columns... [ ID (PK), ID_FACILITY, ID_USER, DATE, HOUR, PAID, PAYMENT_METHOD ]

预订包含以下列... [ID（PK），ID_FACILITY，ID_USER，DATE，HOUR，PAID，PAYMENT_METHOD]

Facility has the following columns... [ ID (PK), NAME, STATE, PRICEPERHOUR, DATE, HOUR ]

设施有以下列... [ID（PK），NAME，STATE，PRICEPERHOUR，DATE，HOUR]

The problem that I have is that I don't know how to compare the values between those two tables. I would like to count the entries in the "Booking" table but checking that the ID_FACILITY value is equal to a given one, like "Swimming Pool" for example, and also check that the hour is "9:00" for example.

我遇到的问题是我不知道如何比较这两个表之间的值。我想计算“预订”表中的条目，但检查ID_FACILITY值是否等于给定的值，例如“游泳池”，并且还检查小时是否为“9:00”。

The wrong query I got so far is this one...

到目前为止我得到的错误查询是这个...

select count(*) as totalbookingcriteria from public.booking, public.facility where hour = '9:00' and name = 'Swimming Pool'

1 个解决方案

#1

What you need is just an INNER JOIN for your tables, so

你需要的只是你的桌子的INNER JOIN，所以

select count(*) as totalbookingcriteria 
  from public.booking b 
     INNER JOIN public.facility f 
           ON ( b.ID_FACILITY = f.ID ) 
 where f.hour = '9:00' 
   and f.name = 'Swimming Pool'