How to show Real Name of User ID by INNER JOIN

[Ion2]

Hi,

I tried to change how content is displayed in the Browse Form similar to this video: http://www.youtube.com/watch?v=Wlob5Fmxzrs&t=0m15s

I used the following code:

Code: Select all

IF(inventory_log_changed_by = '56a0ed05577939c', 'Name of Changer')

The error message was:

ChangeDisplay.png (53.72 KiB) Viewed 4627 times

Then I tried the code from the Wiki: http://wiki.nubuilder.net/nubuilderv3/i ... ms#Display Example 2

The code was:

Code: Select all

DATE_FORMAT(inventory_log_changed_at,'%m-%d-%Y')

The error message was as shown above.

What am I doing wrong? What is the easiest way to show the real name of the Changer?

Greetings,
Timo

[admin]

Ion2,

Your SQL statement will need to inner join to the zzzsys_user table to get the name.

Steven

[Ion2]

Actually I had heavy problems getting this inner join to work. You can make inner joins in multiple ways. This was the only one that worked:

Code: Select all

SELECT inv.*
     , log_added_by_name
     , log_changed_by_name
  FROM inventory inv
       LEFT JOIN (SELECT zzzsys_user_id, sus_name AS log_added_by_name FROM zzzsys_user) zu1
          ON inv.inventory_log_added_by = zu1.zzzsys_user_id
       LEFT JOIN (SELECT zzzsys_user_id, sus_name AS log_changed_by_name FROM zzzsys_user) zu2
          ON inv.inventory_log_changed_by = zu2.zzzsys_user_id
order by inventory_log_changed_at DESC

I hope it can be helpful for somebody.

Greetings,
Timo

[admin]

.