JOIN with LIMIT in Query

Asked

Viewed 66 times

1

I have the following tables with the information below:

RESERVATION:

id| checkin    | checkout   | titular | valor
--+------------+------------+---------+--------
1 | 2018-09-01 | 2018-09-02 | BOOKING | 300
2 | 2018-09-03 | 2018-09-05 | BOOKING | 600

GUESTS:

id| nome    | reserva
--+---------+---------
1 | FULANO  | 1
2 | FULANA  | 1
3 | SICRANO | 2
4 | SICRANA | 2

With this query:

SELECT R.ID, R.TITULAR, R.CHECKIN, R.CHECKOUT, H.NOME FROM RESERVA R
JOIN HOSPEDES H ON H.RESERVA = R.ID

I have the result:

id  titular checkin     checkout    nome
1   BOOKING 2018-09-01  2018-09-02  FULANO
1   BOOKING 2018-09-01  2018-09-02  FULANA
2   BOOKING 2018-09-03  2018-09-05  SICRANO
2   BOOKING 2018-09-03  2018-09-05  SICRANA

I would like a query that returns me only 1 match in the table GUESTS, that is, only the first guest of each reservation.

Thus:

id  titular checkin     checkout    nome
1   BOOKING 2018-09-01  2018-09-02  FULANO
2   BOOKING 2018-09-03  2018-09-05  SICRANO

It is possible to join with limit 1 ?

Segue Fiddle: http://sqlfiddle.com/#! 15/a91b0/2

postgresql

2 answers

1

Use this query. It takes the first guest by ID, ie the smallest ID. For each reservation, it takes the smallest.

 SELECT *
 FROM Reserva r
 JOIN Hospedes h ON r.id = h.reserva
 AND h.id = (SELECT MIN(h2.id) from hospedes h2 where h2.reserva = r.id)

1

Selecting with Distinct on and then using ORDER you get the expected result.

SELECT  Distinct on(R.ID) R.TITULAR, R.CHECKIN, R.CHECKOUT, H.NOME FROM RESERVA R
JOIN HOSPEDES H ON H.RESERVA = R.ID ORDER BY R.ID

  • I can’t sort through the R.ID, I have to sort through the checkin, there’s another way?

  • The first of each reservation would be by ID, right? see my answer if it works.

  • Does your checkin have a single ID per day? Or on the same day (2018-09-01 for example) there may be more than one ID?

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.