CODEWITHSUNDEEP
mysqlsql
Not a machine
Not a machine

Reputation: 551

Querying multiple database tables without using JOIN?

I am attempting to combine three queries into one and failing miserably. Everything goes well until I add the "order by" clause.

SELECT
wti.price, hh.price, mb.price
FROM
spotprices.wti, spotprices.hh, spotprices.mb
ORDER BY
wti.sdate desc limit 1, hh.sdate desc limit 1, mb.sdate desc limit 1;

In essence, I am attempting to get the most recent price by sdate by using the "order by sdate desc limit 1" trick. Individually, the query works well.

SELECT price FROM spotprices.wti ORDER BY wti.sdate desc LIMIT 1;

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

Use subqueries in the select:

SELECT (SELECT wti.price FROM spotprices.wti ORDER BY wti.sdate DESC LIMIT 1) as wti_price,
       (SELECT hh.price FROM spotprices.hh ORDER BY hh.sdate DESC LIMIT 1) as hh_price,
       (SELECT mb.price FROM spotprices.mb ORDER BY mb.sdate DESC LIMIT 1) as mb_price;

Upvotes: 2

Related Questions