I am relatively new to R and to Shiny. This is my first post to the R community, please forgive me if the formatting is not correct. I welcome all criticism in how to better articulate the issue I am trying to resolve.
I have hit a wall in trying to use the value from a cell in a reactive queried table in the WHERE clause for querying another table. Any advice for the situation below would be greatly appreciated.
I am able to generate a query using reactive text input from the user and render a filtered table showing the relevant information:
What I cannot figure out is how to then query off of the value of the id from the user information in another table within the same database that has the id as a foreign key.
SELECT * FROM mysqldb.OtherTable WHERE user_ID = UserTable$Id[1]
Any ideas on how to assign a variable to the charter string value from a reactive table, in a way that it can be referenced in other queries?
Since you asked, let me start by pointing you towards some information on how to best create a good post that people on here can work with. It's formatted as a reprex. A reprex consists of the minimal code and data needed to recreate the issue/question you're having. You can find instructions how to build and share one here:
Now for your question, I'm not sure I completely get it what you want, but I assume that you want the username (provided by the user in the app), to first filter a table to display the correct user information, then use the user's id (after filtering) to get info from another table.
If this is what you like, you can actually solve that with just SQL, no extra R or Shiny code needed:
sprintf("SELECT * FROM mysqldb.OtherTable WHERE user_ID =
(SELECT user_id from mysqldb.UserTable WHERE username = '%s')", input$username)
UserTable is the table that contains the usernames and ids, OtherTable is the one where the id is the foreign key (replace with actual names)
user_id and username are the attributes of those tables (replace with actual names)
The actual query is a nested query where the we first look up the user_id based on the username in one table, then use it as input for querying the next table
Using the sprintf() function (could also be done with paste), we paste the username given in Shiny (input$username, replace with actual textbox id) into the query.
The result should return the data you need from the database.
@pieterjanvc Thank you for the reprex information and for the solution. Using a sub-query is an effective and obvious solution that I should have thought of myself! This worked perfectly. I will still need to find a way to assign attributes from the query to variables for calculation I am using, but that can be saved for another post.
Thank you again for your time.
