SQL Field with specific query | max date |

[bigart]

Hi,
I have two tables:
Clients (parent table) and conversation (sub table).
My goal is to print the max date (date_added) from conversation with customer in parent table.
It's possible ?
If yes how to do it ?


below my sql working query:

Code: Select all

SELECT max(FROM_UNIXTIME(app_entity_54.date_added,'%Y-%m-%d')) FROM app_entity_54
INNER JOIN app_entity_53 ON app_entity_54.parent_item_id=app_entity_53.parent_item_id
WHERE app_entity_54.parent_item_id=10

[bigart]

OK. - I think I did it.

You have to create new filed and choose formula (as the field type)
In my case I had to copy working sql query and change "static customer id" to the choosen customer id automaticly selected from table.

See below:

Code: Select all

SELECT max(FROM_UNIXTIME(app_entity_54.date_added,'%Y-%m-%d')) FROM app_entity_54
INNER JOIN app_entity_53 ON app_entity_54.parent_item_id=app_entity_53.parent_item_id
WHERE app_entity_54.parent_item_id=[id]  

[rubinhc]

Hello bigart,
Thank you for your post. I have similar question like yours, and it helped.
Thank you.

[JANNI]

Hello,
I need to get the last date (updated date) with which a product has been assigned to a customer.

Products (product_id, product type, last movement date, last movement)

Guides (guide_id, client_id, assignment date)
> details (guide_id, product_id, assignment date, type of movement (entry or exit))



SELECT max(FROM_UNIXTIME(app_entity_31.field_282,'%Y-%m-%d')) FROM app_entity_31
INNER JOIN app_entity_29 ON app_entity_31.field_266=app_entity_29.parent_item_id
WHERE app_entity_31.field_266=[id]


I have tried this code but it doesn't give me any results.