MySQL Subquery in Query Builder

[updates]

Hi Everybody,
I m not a so big expert then maybe I m asking something easy, but I don’t find a solution.

I have two tables in MySQL db I used to give access to other lists to the clients.

First table is called: xxxx_user and it is the table where are stored id, username and password to give access the application.
Structure is:

id (INT)
usr_name(varchar)
usr_password(varchar)

The second table is called xxx_regauth and have inside the ID of the Item and the id of the users that has access on it

xxx_regauth

regauth_id(int)
regauth_idregistry(int) //where it is stored the ID of the item)
regauth_iduser(int) // where it is stored the ID of the user

for each item coud be that have multiple users (then will have different lines)

Now the question is the following: I need to list for item X the users that are not authorized. If ound in MySQL the following query, with a Subquery:

SELECT * FROM xxx_user
WHERE xxx_user.id NOT IN (SELECT regauth_iduser FROM xxx_regauth where xxx_regauth.regauth_idregistry = 20);

The question are:

1. How could I set the same query (or get same result) using Query Building of Wappler?

2. How can I pass the GET_id info to the subquery? (where xxx_regauth.regauth_idregistry = 20)

I tried to make a VIEW in MySQL and manage it in wappler like normal query but I got problem to pass the GET_id from ServerConnect to the Subquery.

Maybe I make wrong structure or I don’t know… I m newbie please don’t judge me too bad

Thank you very much

Roberto

[t11]

Hi,
I think what will give you the result you want is to create repeat action inside server connect.

Follow these steps

1.First create your main query
2.Uncheck output option for the query
3.Then you create a repeat and set as expression the first query
4.Then pick the values from the first query that you want to use in the second query
5.Create the second query and use as filter the value/values that you imported from the first query
6.Check the output option

If you need more details just let me know.

Thank you

[updates]

HI,

thank you very much for your solutions, sometimes things are easier than they appear…
I did the same:

1. I inserted GET Variable Id and make the steps as you said:
   

2. Then made the Repeat picking up the table values I needed
   

3. In the second query done inside the repeat, I used the following conditions:
   

   

4. I got the page with listed ALL the users, then I simply used HIDE the line of the table

   if query 2 has items.

5. Got the result.

Is that ok Nikolas ?
thank you very much for your support !!

Have good summer
Roberto

[t11]

Yes this is the idea, if it displays the results you need then this is it. Just in step 2 uncheck the output option

[updates]

THANK You very much!!