MYSQL -从匹配的两个表中获取值

I am trying to get a value from two tables on match.

我试图从匹配的两个表中获取值。

Booking Table:

预订表:

 id, member_id, salon_id, service_id, appointment_date, booking_date
  1,         5,        2,          1,       2015-08-29,   2015-08-29
  2,         5,        2,          2,       2015-08-29,   2015-08-29
  3,         5,        3,          3,       2015-08-29,   2015-08-29
  4,         5,        3,          4,       2015-08-29,   2015-08-29

Services Table:

服务表:

 service_id, salon_id, service_name, price
          1,        2, Make-Up     , 25
          2,        2, Hair Styling, 10
          3,        3, Waxing      , 15
          4,        3, Threading   , 20

Current Query:

当前查询:

SELECT b.salon_id 
     , s.service_id
     , s.price
     , b.booking_date 
  FROM bookings b
  JOIN services s
    ON b.salon_id = s.salon_id 
   AND b.salon_id = 2 
   AND b.booking_date = '2015-08-29';

Current Result:

目前的结果:

  salon_id, service_id, price, booking_date
         2,          1,    25,   2015-08-29
         2,          1,    25,   2015-08-29
         2,          2,    10,   2015-08-29
         2,          2,    10,   2015-08-29

Expected Result:

预期结果:

 salon_id, service_id, price, booking_date
        2,          1,    25, 2015-08-29
        2,          2,    10, 2015-08-29

What i am trying to do is. I want all the specific salon booking for specific date. After that i will sum price to get total sales of salon for specific date.

我想做的是。我想订所有特定日期的沙龙。之后我会对价格进行汇总，得到具体日期的沙龙总销售额。

Any help please?

任何帮助吗?

Thanks in advance.

提前谢谢。

3 个解决方案

#1

I think you also need a condition on the services id in the join:

我认为您还需要在连接中的服务id上有一个条件:

SELECT b.salon_id, s.service_id, s.price, b.booking_date
FROM bookings b INNER JOIN
     services s
     ON b.salon_id = s.salon_id AND
        b.service_id = s.service_id
WHERE b.salon_id = 2 AND
      b.booking_date = '2015-08-29';

Also notice how table aliases make the query easier to write and to read.

还要注意表别名如何使查询更容易编写和读取。

#2

Try DISTINCT:

试试不同的:

SELECT DISTINCT bookings.salon_id, services.service_id, services.price,
bookings.booking_date FROM bookings INNER JOIN services ON
bookings.salon_id=services.salon_id AND bookings.salon_id = 2 AND
bookings.booking_date = '2015-08-29';

#3

Try this query:

试试这个查询:

SELECT bookings.salon_id, services.service_id, services.price,bookings.booking_date 
FROM bookings 
LEFT JOIN services ON bookings.salon_id=services.salon_id AND bookings.service_id = services.service_id;

This query will display data from booking that exist in service.

此查询将显示服务中存在的预订数据。

bookings.salon_id=services.salon_id AND bookings.salon_id = 2 AND bookings.booking_date = '2015-08-29';

bookings.salon_id =服务。salon_id和预订。salon_id = 2和booking。booking_date = ' 2015-08-29 ';

make your query not dynamic. How if you want to get salon_id 1 or 2 or 3 or all of them?

使查询不是动态的。如何获得salon_id 1或2或3或所有这些?

#1