How to fetch data from multiple tables using view in Mysql

Question

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 :

**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

'wanna fetch data where created_at( column ) between date1 and date2' - thats pretty much the syntax so what problem are you having? — P.Salmon
– P.Salmon, Commented May 6, 2020 at 8:51
for example : i want fetch records that created between 2019-12-01 and 2020- 01-01 — ali dehqani
– ali dehqani, Commented May 6, 2020 at 8:58
"TSQL" and MySQL refer to different databases. I'm confused by the title. The code suggests MySQL. — Gordon Linoff
– Gordon Linoff, Commented May 6, 2020 at 11:24

P.Salmon · Accepted Answer · 2020-05-06 09:19:10Z

1

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

answered May 6, 2020 at 9:19

P.Salmon

17.7k2 gold badges14 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to fetch data from multiple tables using view in Mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related