Question 268144 in Mysql Question posted in
The official MySQL documentation can be found here.

Why does this query work in MySQL but fail in MariaDB?

This query works as intended in MySQL, but fails in MariaDB as not being able to recognize the "uID" field.
Why? Is it possible to code one version for both?

select user_id as uID, last_name, first_name, athlete, parent, coach, code,
   sum(points) as total,
   propoints as lifetime,
   (select count(*) / 7 from 
        (select count(user_id), Date(created_at) as date
          from user_propoints
          where user_id = uID
          group by date) as ac1)
    as activity
from user_propoints
left join users on users.id = user_id
left join countries on users.country = countries.id
group by user_id

order by total desc

Output from MySQL:

21530 Mazzucchelli Lou 0 0 0 NULL 37 37 0.5714

Output from MariaDB:

Error in query (1054): Unknown column ‘uID’ in ‘where clause’

Tags:

2

Answers


  1. 0 
    select user_id as uID, last_name, first_name, athlete, parent, coach, code,    sum(points) as total,    propoints as lifetime,    (select count(*) / 7 from 
        (select count(user_id), Date(created_at) AS DATE
          from user_propoints
          where user_id = user_propoints.user_id  -- Join the subquery using user_id
          group by date) as ac1)
    as activity from user_propoints left join users on users.id = user_id left join countries on users.country = countries.id group by user_id order by total desc;

    In this modified query, I replaced ‘where user_id = uID’ with ‘where user_id = user_propoints.user_id’ in the subquery.

    Login or Signup to reply.
  2. 0

    When you have multiple tables in a query you should alias your tables and qualify the column names with the table aliases. As you have not provided the DDL for your tables, I have guessed and you can correct as required.

    Your left joins should probably be inner joins, as user_propoints for a user that does not exist does not make sense. Similarly, as long as users.country does not allow nulls, it should be an inner join.

    I think you are over-complicating your query with the correlated subquery. Surely, this should provide the desired result:

    select
    u.user_id as uID, u.last_name, u.first_name, u.athlete, u.parent, u.coach,
    c.code,
    sum(up.points) as total,
    u.propoints as lifetime,
    count(distinct date(up.created_at)) / 7 as activity
from user_propoints up
join users u on u.id = up.user_id
join countries c on u.country = c.id
group by u.user_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search