JOIN with LIMIT in Query

Navigation
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

HOSPEDES : 

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 only 1 match in the table HOSPEDES , that is, only the first host of each reservation.

So: 

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

Is it possible to make a JOIN with LIMIT 1?

Follow Fiddle: link

    
asked by anonymous 26.10.2018 / 21:18

2 answers

1

Use this query. It takes the first guest by the ID, that is, the lowest ID. For each reservation, take the smaller one. 

 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)
    
26.10.2018 / 21:35
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
    
26.10.2018 / 21:37
Optimization of php code (NF key generation) Select2 in several fields with the same name