CODEWITHSUNDEEP
phpmysqlsqljoinselect
neox
neox

Reputation: 23

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

I have three tables in mySQL database and I want to get only 1 LAST ENTERED DATA from all three 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 an error and this is not a good way of doing it..soo how to do it?.

There is no relation between the tables because tables are updated randomly by 2 MCU-s that upload data in different time so the time, id autoincrement 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.

Upvotes: 0

Views: 49

Answers (2)

Mureinik
Mureinik

Reputation: 311798

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

Upvotes: 1

siggi_pop
siggi_pop

Reputation: 647

SELECT  tb1.temp as temp1, tb1.hum  as hum1, 
    tb2.temp AS temp2, tb2.hum AS hum2,   
    tb3.spremnik AS status_spremnik
FROM 
(SELECT * FROM dnevna ORDER BY id DESC LIMIT 1) AS tb1,
(SELECT * FROM podrum ORDER BY id DESC LIMIT 1) AS tb2,
(SELECT * FROM spremnik_status ORDER BY id DESC LIMIT 1) AS tb3

Upvotes: 0

Related Questions