Hi, i’m trying to only get one referenced item by id from table (users) in my table (transactions).
This is only returning one transaction ^ I would like to return all transaction items but only return the users connected to each transaction and not call any more than that.
Can someone guide me please
users[0].userid
the [0] refers to the first item in the array of responses.
you could try this:
users.userid.where(‘col-name’, your-desired-value, ‘==’).split(’,’)
this will return the items in an array which the query can then read with an “IN” condition.
you can add multiple where conditions - all the where clauses will work as “AND” connector.
Hi thanks for you reply, I don’t understand what I should replace your-desired-value with.
The expression is showing up errors for your quotation character.
This isn’t working for me
users.userid.where('user', 'userid', '==').split(',')
Sometimes it’s difficult for people not used to SQL to explain others what output they want from their actual schema.
In those cases I always suggest using http://sqlfiddle.com/ where people can create schemas with sample data and others can help providing the needed output.
Users[0].userid is the first element of the Users array which seems to be your parameter. So you are returning all transactions by that single user. Which seems to be only one user by the output.
How is your parameter formed?
Thanks for the reply, I understand 0 will return first item, I only submit that because it’s the only preprogrammed query. I expected to query a joined table by reference to be easily managed by a wappler click/select query.
Sqlfiddle is great but I don’t know how to export the schema from wappler or how to recreate there.
I’m not sure what you mean by “How is your parameter formed?”
I will try to explain better what I would like to do with this query.
I have two tables in this query, the first is transactions & it’s joined by the user table on the reference field of transaction.transactionuser.
Each transaction item has a user associated to it, I would like to call all transaction items but only call the user items linked with those transactions. If a user is not associated with a transaction it should not be called.
Ok. You need an inner join instead of left join.
Thanks for the clarification, Am I getting close? 
users.where('users.userid', '==', 'transaction.transactionuser'.split(','))
Do I need to have the user.userid field on the left of the in operation or the right?
SELECT *
FROM transaction
INNER JOIN user ON (transaction.transactionuser = user.userid)
This will return all fields from both tables and will use transactionuser and userid to match the rows. In other words: how do I glue the rows and what data I present.
So the above query will output all transactions for all users that exist in both tables. So if there is a transaction without user it will not output it.
If a transaction row has a transactionuser that doesn’t have a match userid in the user table it will not output it the row as there is no intersection.
LEFT JOIN will output all rows from the left join even if there is no match on the right table.
RIGHT JOIN will do the contrary. Output all rows from the right table even if there is no march on the left table.
Once that is clear then you use the WHERE clause to add filters or conditions. Like for example filter by one user, by dates, by amounts, etc
If you want to show only transactions for user “123” and all the info associated from both tables you would do.
SELECT *
FROM transaction
INNER JOIN user ON (transaction.transactionuser = user.userid)
WHERE transaction.transactionuser = ‘123’
I understand now, thank you for explaining.