javimuu javimuu - 1 year ago 40
SQL Question

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!

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download