0

I want to count landingpage views that happened 7 days before purchases. I used this query and its give an error

"cannot load from mysql.proc. the table is probably corrupted"

SELECT count(l.customer_id) FROM customer_profiles.purchase p
left join landingpage_view lp on lp.customer_id = p.customer_id
where lp.datetime between date_sub(p.datetime(), INTERVAL 7 Day) and p.datetime()
and lp.customer_id = '14475'
  • Possible duplicate of [How to get the number of days of difference between two dates on mysql?](https://stackoverflow.com/questions/2490173/how-to-get-the-number-of-days-of-difference-between-two-dates-on-mysql) – Valerica Nov 03 '17 at 10:09
  • Maybe your table is corrupted. Have you tried repairing it? – Martin Nov 03 '17 at 10:10
  • You have a database called customer_profiles? – Strawberry Nov 03 '17 at 10:11
  • Yes I have database called customer_profiles. Why? –  Nov 03 '17 at 10:20
  • no table is correct. when i run this query only then its give me an error. –  Nov 03 '17 at 10:24

1 Answers1

1

Start with a syntactically correct query:

select count(*) 
from customer_profiles.purchase p join
     landingpage_view lp
     on lp.customer_id = p.customer_id
where lp.datetime between date_sub(p.datetime(), interval 7 Day) and p.datetime() and
      lp.customer_id = 14475;

The only change that would affect an error is in the count(). The alias l is not defined.

Other changes:

  • You might as well use count(*). There is no need to count a column because you seem to want a count of the rows.
  • The where clause turns the left join into an inner join. So, use the join the query is really doing.
  • I am guessing that customer_id is a number, so I removed the single quotes.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • its still give me same error "cannot load from mysql.proc. the table is probably corrupted" –  Nov 03 '17 at 10:18
  • @user6186914 . . . In that case, the table is probably corrupted. Given that you were getting this error on a query that was syntactically incorrect, the corruption would appear to be in the metadata. The only advise I can give you is to recreate the table (from either a backup or original data). – Gordon Linoff Nov 03 '17 at 11:13