mySQL connect 3 tables with no relation and take last data from them

3646 views php
2

this is my first post here so don't be rude because I was searching for this ansver for 3 days and can't find what I need. Also Im new in sql and php.

So.. I have 3 tables in mySQL database and I want to get only 1 LAST ENTERED DATA from all 3 tables (last entered data from table1, last entered data from table2, last entered data from table3). I was able to get first entered data of all tables this way:

SELECT dnevna.temp AS temp1, dnevna.hum AS hum1, podrum.temp AS temp2, podrum.hum AS hum2, spremnik_status.spremnik AS status_spremnik
FROM dnevna, podrum,spremnik_status
LIMIT 1

so then I tried for last entered data this way:

SELECT dnevna.temp AS temp1, dnevna.hum AS hum1, podrum.temp AS temp2, podrum.hum AS hum2, spremnik_status.spremnik AS status_spremnik
FROM dnevna, podrum, spremnik_stauts
ORDER BY dnevna.id, podrum.id,spremnik_stauts.id DESC
LIMIT 1

but I got error and this is not a good way of doing it..soo how to do it?.

Tehere is no relation between the tables becasue tables are updated randomly by 2 MCU-s that upload data in difeternt time so the time, id auto increment etc. can't match. Also I have in 2 of 3 tables same column names (temp, hum are the same column names in 2 tables..so is that a problem? Do i need to rename?)

I need that sql select to make ONE php JSON string from 3 tables so I can encode it later for Android studio, MCU-u reading etc.

Thansk!

answered question

Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

1 Answer

3

You can retrieve the last record from each table and cross-join those three queries:

SELECT     *
FROM       (SELECT   temp AS temp1, hum AS hum1
            FROM     dnevna
            ORDER BY id DESC
            LIMIT    1) a
CROSS JOIN (SELECT   temp AS temp2, hum AS hum2
            FROM     podrum
            ORDER BY id DESC
            LIMIT    1) b
CROSS JOIN (SELECT   spremnik AS status_spremnik
            FROM     spremnik_status
            ORDER BY id DESC
            LIMIT    1) c

posted this

Have an answer?

JD

Please login first before posting an answer.