CODEWITHSUNDEEP
mysqlsqllaravel
ali dehqani
ali dehqani

Reputation: 184

How to fetch data from multiple tables using view in Mysql

this is my view : 

CREATE VIEW  Statistic as
SELECT
  (SELECT
     COUNT(0)
   FROM users) AS `tot_user`,
  (SELECT
     COUNT(0)
   FROM demands) AS `tot_demand`,
  (SELECT
     COUNT(0)
   FROM reports) AS `tot_report`,
  (SELECT
     COUNT(0)
   FROM users
   WHERE users.mobile_verified_at IS NULL) AS `user_inactive`,
  (SELECT
     COUNT(0)
   FROM reports
   WHERE reports.state =0) AS `report_inactive`,
  (SELECT
     COUNT(0)
   FROM demands
   WHERE demands.state = 1) AS `demand_inactive`,
  (SELECT COUNT(*) FROM replies LEFT JOIN demands ON demands.id=replies.id) AS `demand_replied`

i wanna fetch data where created_at( column ) between date1 and date2

this is my tables structure :

demand table

user table

**And so are the other tables

for example : i want fetch records that created between 2019-12-01 and 2020- 01-01

request(data1 and date 2 from the server) -> send ->database | Database => Return this view

Upvotes: 0

Views: 161

Answers (1)

P.Salmon
P.Salmon

Reputation: 17615

You want to compare a timestamp to a date so use date function

where date(created_at) between '2019-12-01' and '2020-01-01'

and do review mysql date functions. https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

Upvotes: 1

Related Questions