Welcome to the nuBuilder Forums!

Join our community by registering and logging in.
As a member, you'll get access to exclusive forums, resources, and content available only to registered users.

Proper SQL for 'Full Name' list Topic is solved

Questions related to customising nuBuilder Forte with JavaScript or PHP.
Post Reply
Paul
Posts: 180
Joined: Mon Aug 25, 2025 6:03 am
Has thanked: 34 times
Been thanked: 5 times

Proper SQL for 'Full Name' list

Unread post by Paul »

I need a SQL query that gives me a list of all 'Full Name' where 'Access Level' = 'access1'
Can someone please help me with that?
kev1n
nuBuilder Team
Posts: 4603
Joined: Sun Oct 14, 2018 6:43 pm
Has thanked: 77 times
Been thanked: 549 times
Contact:

Re: Proper SQL for 'Full Name' list

Unread post by kev1n »

Try:

Code: Select all

SELECT 
    u.sus_name
FROM 
    zzzzs​​ys_user AS u
INNER JOIN 
    zzzzs​​ys_access AS a 
    ON u.sus_zzzzsys_access_id = a.zzzzsys_access_id
WHERE 
    a.sal_code = 'access1';
Depending on where the SQL will be used (e.g., in a SELECT statement or another context), you may need to adapt it accordingly.
Paul
Posts: 180
Joined: Mon Aug 25, 2025 6:03 am
Has thanked: 34 times
Been thanked: 5 times

Re: Proper SQL for 'Full Name' list

Unread post by Paul »

This code:

Code: Select all

SELECT 
    u.sus_name
FROM 
    zzzzsys_user AS u
INNER JOIN 
    zzzzsys_access AS a 
    ON u.sus_zzzzsys_access_id = a.zzzzsys_access_id
WHERE 
    a.sal_code = 'access1';
works perfectly when run directly in my database
Result:
sus_name
>Client One
>John Smith
>Next User

but I get 'undefined' three times (one for each existing value) when run from a Select object in nubuilder.
undefined.png
You do not have the required permissions to view the files attached to this post.
kev1n
nuBuilder Team
Posts: 4603
Joined: Sun Oct 14, 2018 6:43 pm
Has thanked: 77 times
Been thanked: 549 times
Contact:

Re: Proper SQL for 'Full Name' list

Unread post by kev1n »

The Select Object expects one of these formats...
Screenshot 2025-10-15 083659.png
You do not have the required permissions to view the files attached to this post.
Paul
Posts: 180
Joined: Mon Aug 25, 2025 6:03 am
Has thanked: 34 times
Been thanked: 5 times

Re: Proper SQL for 'Full Name' list

Unread post by Paul »

Got it:

Code: Select all

SELECT sus_name, sus_name
FROM zzzzsys_user
WHERE sus_zzzzsys_access_id = '68d859af85961bb'
ORDER BY sus_name
I only want the unique names, not the ID's so I had to use sus_name twice to satisfy nuBuilder's requirement. The key item is to know what sus_zzzzsys_access_id to use. '68d859af85961bb' is the Access Level called 'access1'.
Post Reply