0

First query is for select institutions and second is for get latest review to this institution.

First query:

SELECT 
    instituteId, 
    instituteName, 
    description 
FROM institutions 
WHERE instituteId IN ('1','2','3')";

Second query:

SELECT 
    name,
    review, 
    timestamp 
FROM reviews 
WHERE instituteId='1' 
ORDER BY timestamp DESC 
LIMIT 1;
Grant
  • 2,413
  • 2
  • 30
  • 41
kumar
  • 159
  • 8

4 Answers4

1
SELECT i.instituteId, 
       i.instituteName, 
       i.description ,
       r.name,
       r.review, 
       r.timestamp
FROM institutions i
INNER JOIN review r
ON i.instituteid = r.instituteId
INNER JOIN (SELECT instituteId,
                   MAX(timestamp) as timestamp
            FROM reviews
            GROUP BY instituteId 
            ) r1
ON r.instituteid = r1.instituteId 
AND r.timestamp = r1.timestamp
WHERE instituteId IN ('1','2','3')
Jay Shankar Gupta
  • 5,918
  • 1
  • 10
  • 27
  • SELECT i.instituteId, i.instituteName, i.description , r.name, r.review, r.timestamp FROM institutions i INNER JOIN review r ON i.instituteId = r.instituteId INNER JOIN (SELECT instituteId, MAX(timestamp) as timestamp FROM reviews GROUP BY instituteId ) r1 ON r.instituteId = r1.instituteId AND r.timestamp = r1.timestamp WHERE instituteId IN ('1','2','3') – kumar Apr 11 '18 at 10:48
  • For large data set I think group by will take longer time than my answer. – Md. Mahmud Hasan Apr 11 '18 at 10:49
1

There are many ways to do that. But the following one gives me the best performance in a large data set. And yes do not forget to indexing the columns. 

SELECT i.instituteId, 
   i.instituteName, 
   i.description ,
   r.name,
   r.review, 
   r.timestamp
FROM institutions i
LEFT JOIN reviews r
ON i.instituteid = r.instituteId and r.timestamp=
(SELECT MAX(timestamp)
        FROM reviews where reviews.instituteId = r.instituteId 
        ) 
WHERE instituteId IN ('1','2','3')
Md. Mahmud Hasan
  • 1,033
  • 1
  • 7
  • 24
0
select i.instituteId, i.instituteName, i.description,
r.name,r.review, r.timestamp
from institutions i inner join reviews  r on i.instituteId=r.instituteId
WHERE instituteId IN ('1','2','3') 
ORDER BY timestamp DESC LIMIT 1
Kedar Limaye
  • 1,041
  • 8
  • 15
0

You can join two tables on a point that both the tables match the same value and property

Syntax: SELECT column_name(s)FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

Solution: SELECT i.instituteId, i.instituteName, i.description, v.name,v.review, v.timestamp FROM institutions i INNER JOIN reviews v on i.instituteId=v.instituteId WHERE i.instituteId IN ('1','2','3') AND v.instituteId=1 ORDER BY v.timestamp DESC LIMIT 1;

Kennedy Oyoo
  • 7
  • 5