I have two tables. They are not related except they share a common phone number columns.
Table one : “MessageStatus” has cols (fromPhone, id, sentDate, direction)
Table two : “UserContacts” has cols (firstName, LastName, phone)
I would like to get all rows from “MessageStatus” table where “direction = inbound” and “firstName” and “LastNmae” for those Inbound messages where “MessageStatus.fromPhone” matches “UserContacts.phone”.
This is what I wrote in “where” clause, but it is giving me error:
fromPhone in (UserContacts[phone=‘+1212345456’].phone).UserContacts.firstName
error:
{
“code”: 1017,
“message”: “Where clause ‘fromPhone in (UserContacts[phone=’+1212345456’’ is invalid”
}
Please help. My app id 94496922-170D-4236-FF64-228CA5F5DE00 thanks.
The following query worked for me (I replaced part of the phone number here with X):
fromPhone in (UserContacts[phone = '+1516XXXXXX92'].phone) and direction = 'inbound'
However, I think this is going to be overengineering. Since you already know the phone number (it goes in the query you tried), the where clause can be simplified:
fromPhone '+1516XXXXXX92' and direction = 'inbound'
Mark
Hi Mark,
Thank you for this. I would like to get the firstName and lastName associated with the phone number that I am passing in the query. These two properties are in “UserContacts” table. How can I these? thank you.
In this case, you need to retrieve the data from the UserContacts table and structure the where clause accordingly. The problem with retrieving data from MessageStatus is that you cannot get properties in the response from unrelated tables.
Regards,
Mark
