CODEWITHSUNDEEP
sqlpostgresql
javimuu
javimuu

Reputation: 1859

SQL JOIN and limit result with some conditions in second table

I have two tables.

Portfolios:

+----+-------+--------------+
| id | name  | created_date |
+----+-------+--------------+
| 1  | Port 1| 2017/08/12   |
+----+-------+--------------+
| 2  | Port 2| 2017/08/14   |
+----+-------+--------------+
| 3  | Port 3| 2017/08/15   |
+----+-------+--------------+

Photos :

+----+------------+--------------+--------------+-----------------+
| id | Port_name  | port_id 　　　| user_id 　　　| created_at      |
+----+------------+--------------+--------------+-----------------+
| 1  | Port 1     |          1   |       null   |    2017/08/10   |
+----+------------+--------------+--------------+-----------------+
| 2  | Port 2     |          2   |       null   |    2017/08/11   | 
+----+------------+--------------+--------------+-----------------+
| 3  | Port 3     |          3   |       null   |    2017/08/12   |
+----+------------+--------------+--------------+-----------------+
| 4  | Port 1     |          1   |          1   |    2017/08/13   | 
+----+------------+--------------+--------------+-----------------+
| 5  | Port 2     |          2   |          1   |    2017/08/14   |
+----+------------+--------------+--------------+-----------------+
| 6  | Port 3     |          3   |          1   |    2017/08/15   | 
+----+------------+--------------+--------------+-----------------+
| 7  | Port 2     |          2   |          1   |    2017/08/16   |
+----+------------+--------------+--------------+-----------------+
| 8  | Port 3     |          3   |          1   |    2017/08/17   |
+----+------------+--------------+--------------+-----------------+
| 9  | Port 2     |          2   |          1   |    2017/08/18   |
+----+------------+--------------+--------------+-----------------+
|10  | Port 3     |          3   |          1   |    2017/08/19   |
+----+------------+--------------+--------------+-----------------+

How can I query to get result like below:

Result:

+---------+------------+----------------+-----------------+
| Port_id | port_name  | photo_id       | photo_created_at|
+---------+------------+----------------+-----------------+
| 2       | Port 2     |       7        |    2017/08/16   | 
+---------+------------+----------------+-----------------+
| 3       | Port 3     |       8        |    2017/08/17   |
+---------+------------+----------------+-----------------+

What I want to do is join Portfolios table with Photos table, and limit result of Photos table with some conditions:

  1. user_id is not null
  2. created_at is "second oldest".

"second oldest" meaning is, for example, I have 4 datetimes: 2017/08/11, 2017/08/12, 2017/08/16, 2017/08/19.

In this case, the "second oldest" is 2017/08/12.

I have tried by myself:

SELECT p.id as Port_id, 
       p.name as Port_name, 
       ph.id as photo_id, 
       ph.created_at as photo_created_at 
FROM "Portfolios" AS p
LEFT JOIN (
          SELECT * 
          FROM "Photos" 
          WHERE user_id IS NOT NULL 
          ORDER BY created_at ASC 
          LIMIT 1 OFFSET 1) as ph
ON p.id = ph.port_id;

and googled for this but find nothing to resolve my problem.

Any help? Thanks in advances!

Upvotes: 1

Views: 91

Answers (2)

Jonathan Willcock
Jonathan Willcock

Reputation: 5245

Like Radim, I do not understand the need to join to portfolios, as this does nothing. My solution uses the row_number() function:

SELECT port_id, port_name, id as photo_id, created_at as photo_created_at FROM
(select *, row_number() OVER (PARTITION BY port_id ORDER By created_at) AS rn from 
photos WHERE user_id IS NOT NULL) r
WHERE r.rn = 2

However, there would be sense in linking to portfolios if port_name was not repeated in photos! Theoretically, port_name should not be in photos!

Edit

If you remove port_name from photos then you need:

SELECT r.port_id, p.port_name, r.id as photo_id, r.created_at as photo_created_at FROM
(select *, row_number() OVER (PARTITION BY port_id ORDER By created_at) AS rn from 
photos WHERE user_id IS NOT NULL) r INNER JOIN portfolios p on p.id = r.port_id
WHERE r.rn = 2

Upvotes: 1

Radim Bača
Radim Bača

Reputation: 10701

I have used two GROUP BY constructs. I have omitted the Portfolios table since it does not seems to be important

select photos.port_id, min(photos.id), min(photos.created_at)
from photos
join 
(
  select port_id, min(created_at) photos_ca_min
  from photos
  where user_id is not null
  group by port_id
) p on p.port_id = photos.port_id
where photos.created_at > p.photos_ca_min and user_id is not null
group by photos.port_id

SQLFiddle

Upvotes: 1

Related Questions