Read data form two tables at the same time

[Ahd]

Hi ALL,
I am wondering if it is possible to make an " inner join" between two tables in the function node. For example, I have two tables and I want to display the data from both tables at a certain condition( if id=id) as shown below :

msg.topic=
"SELECT * FROM Check_Out JOIN Check_IN ON Check_Out.id = Check_IN.id";
return msg;

Is that possible?

[Steve-Mcl]

@Ahd

two tables in the function node

SQL operations - in a JS function? What/Where is this table (do you already have data from the DB)? What type of database are you referring to?

@Ahd

msg.topic=
"SELECT * FROM Check_Out JOIN Check_IN ON Check_Out.id = Check_IN.id";
return msg;

Is that possible?

Of course setting msg.topic to a SQL like string is possible (you can set a string to anything stringy)

EDIT...
Let guess as you haven't provided details...
you're working with MYSQL? And you wish to know if you can send a SQL string (in msg.topic) that contains a SQL JOIN and get data back from MYSQL?

Give it a go & see if it works.

[Ahd]

well , my story is that i I have two tables in my database first is "check in" and the second is "checkout" as shown below. I have access to these tables from node-red. What I want to do is to make the name, time in and time out in one message (under one payload )on the node-red . As I have a name filed in each table as PK.

So I tried to make that by writing the following query in the function node but it showed nothing.

msg.topic=
"SELECT * FROM Checkin JOIN Checkout ON checkin.name= Checkout.name";
return msg;

table 1and table 2:

[Ahd]

@Steve-Mcl

thanks, it solved

[Steve-Mcl]

Glad you solved it.

It is considered good forum etiquette to you post what you did for others to benefit from.

If you do, don't forget to list any external nodes you use in the solution (e.g. node-red-contrib-some_database_sql_node)

[Ahd]

Sure, I dont mind to share the information .

here is the flow

[{"id":"b2105280.bde26","type":"tab","label":"Flow 5","disabled":false,"info":""},{"id":"92c4141e.80e058","type":"function","z":"b2105280.bde26","name":"read from two tables","func":"msg.topic= \n\"SELECT * FROM Checkin JOIN checkout ON Checkin.name = checkout.name\"; \nreturn msg;\n\n//msg.topic=\"select * from Check_Out \"","outputs":1,"noerr":0,"x":420,"y":200,"wires":[["be7ba06.dbe766"]]},{"id":"be7ba06.dbe766","type":"mysql","z":"b2105280.bde26","mydb":"2cd1ee93.18a152","name":"DB","x":610,"y":200,"wires":[["82f0348b.fe9fb8"]]},{"id":"ccd72ada.f45928","type":"inject","z":"b2105280.bde26","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":220,"y":200,"wires":[["92c4141e.80e058"]]},{"id":"82f0348b.fe9fb8","type":"debug","z":"b2105280.bde26","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":790,"y":200,"wires":[]},{"id":"2cd1ee93.18a152","type":"MySQLdatabase","z":"","name":"yyyyyyyyyyyyyyyy","host":"XXXXXXXX","port":"3306","db":"XXXXXXX","tz":""}]

type or paste code here

[Steve-Mcl]

You didnt say what the node type was (exact contrib name please - you get it from the pallet) - without the full name, others cant replicate what you have.