Need help with SQL query - stuck since days

What you're describing is not possible.

t_user has many t_item and ant_item has one item_meta

So if this is true, then there are many item_meta.s_value for each t_user.

You see - how do you plan to choose which of those values to set as t_user.phone_mobile?

Hi @burlresearch , yes you're right. However, the the field item_meta.s_value will have the same value for each user.

So, in simple words, each time an ITEM is created, for a particular user, the item_meta.s_value will be same each time.

Thank you..

OK - as long as you're sure this is the case, then we can try.

Since you already have the users you don't want to INSERT new rows in t_user - you are looking for an UPDATE here - you just want to put the phone on the existing record.

So that means our query is going to be something like: update t_user SET s_phone_mobile = item_meta.s_value

Now we just have to figure out how to join that item_meta. Since there are many meta rows foreach, we're going to have to pick [any] one. Since they're all the same, it won't matter which.

With MySQL that pretty much means we need a GROUP BY, or LIMIT 1. I prefer grouping:

So then we can get item_meta joined to their t_items and group the results by the user_id - so we only get one:

SELECT *
FROM t_item i
  JOIN item_meta m ON i.pk_i_id = m.fk_i_item_id
GROUP BY fk_i_user_id

You should be able to run that query and see the rows - one per user_id and there will be a single field of m.s_value... that's what we need for our UPDATE. So we're in business, now we can combine them:

UPDATE t_user
SET s_phone_mobile = (
  SELECT m.s_value
  FROM t_item i
    JOIN item_meta m ON i.pk_i_id = m.fk_i_item_id
  WHERE i.fk_i_user_id = t_user.pk_i_id
  GROUP BY fk_i_user_id
)

and you should be good.

HOLY cow dude, that works like a charm!! Thank you @burlresearch , you saved my life! :) :)

Hello @burlresearch , the query ran fine on my local machine, but does not run on the server.

This is the error i see :

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'DBNAME.m.s_value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Any ideas? Thanks.

No worries, here was the solution :)

https://medium.com/@kasunsiyambalapitiya/fixing-this-is-incompatible-with-sql-mode-only-full-group-by-in-mysql-de811ed35ae9

You could use a LIMIT 1 variation in the subquery, if the group-by isn't portable. It's an option.

Thanks!