search

Home  >  Q&A  >  body text

Select from two tables with inner join and constraints

I have two tables Service and Status. The service table only saves a name and an id

| id |  name |
|----|-------|
|  1 | Test1 |
|  2 | Test2 |

There is also a state table like this

| id | status | service_id |                 timestamp |
|----|--------|------------|---------------------------|
|  1 |     OK |          1 | October, 15 2015 09:03:07 |
|  2 |     OK |          1 | October, 15 2015 09:08:07 |
|  3 |     OK |          2 | October, 15 2015 10:05:23 |
|  4 |     OK |          2 | October, 15 2015 10:15:23 |

I want to get such data

| id |  name | status |                 timestamp |
|----|-------|--------|---------------------------|
|  1 | Test1 |     OK | October, 15 2015 09:08:07 |
|  2 | Test2 |     OK | October, 15 2015 10:15:23 |

Latest status with service data. I have tried this argument

SELECT ser.id, ser.name, a.status, a.timestamp
from Service ser
  inner join (select * from status
              order by Status.timestamp
              DESC limit 1) as a
    on a.service_id = ser.id

but I only get

| id |  name | status |                 timestamp |
|----|-------|--------|---------------------------|
|  2 | Test2 |     OK | October, 15 2015 10:15:23 |

How do I change the statement to get what I want?

For testing SQL Fiddle

P粉103739566P粉103739566336 days ago351

reply all(2)I'll reply

  • P粉023650014

    P粉0236500142023-12-31 18:20:46

    For each service, only use if no subsequent service exists NOT EXISTS Return status:

    select ser.id, ser.name, st.status, st.timestamp
    from service ser
      left join status st1 on ser.id = st1.service_id
    where not exists (select 1 from status st2
                      where st2.service_id = st1.service_id
                        and st2.timestamp > st1.timestamp)

    You can choose to execute LEFT JOIN to return a service without any status. If not required, switch to JOIN.

    reply
    0
  • P粉752826008

    P粉7528260082023-12-31 13:53:21

    You can do this:

    SELECT 
      ser.id, 
      ser.name, 
      s.status, 
      s.timestamp 
    FROM Service ser 
    INNER JOIN status as s ON s.service_id = ser.id
    INNER JOIN
    (
       SELECT
         service_id, 
         MAX(timestamp) AS MaxDate
       FROM status 
       GROUP BY service_id
    ) AS a  ON a.service_id = s.service_id 
           AND a.MaxDate = s.timestamp;

    Join with subquery:

    SELECT
      service_id, 
      MAX(timestamp) AS MaxDate
    FROM status 
    GROUP BY service_id

    Will eliminate all statuses except the latest date.

    reply
    0
  • Cancelreply