Join tables but keep matches in a single row I have the following tables structure in a PostgreSQL DB:


These tables have this content:



So if I want to retrieve the SB associated to each server I'll need to join these 3 tables:


Server with Map_ServerSB on server.Id = Map_ServerSB.IdObj1 
Map_ServerSB with SB on Map_ServerSB.IdObj1 = SB.Id


The thing is that, this way, I'll get 2 records.

However, I would need to get a single record; something like this:



Note: We won't have more than 2 records in SB for each server (there could be zero, one or two records for each server)

Is there any way to accomplish that?

Thanks!
You need either 2 more joins, or to do a GROUP BY first (to collapse the two rows into one) and then join Map_ServerSB and SB (twice). In a query:

SELECT
    s.Id     AS ServerId,
    s.Name   AS Server_Name,
    sb1.Name AS SB_1,
    sb2.Name AS SB_2
FROM
    (
        SELECT 
           ms.IdObj1,
           MIN(ms.IdObj2)
               AS sb_id_1,
           CASE WHEN MIN(ms.IdObj2) &lt;&gt; MAX(ms.IdObj1)
               THEN MAX(ms.IdObj1) ELSE NULL
           END
               AS sb_id_2
        FROM 
            Map_ServerSB AS ms
        GROUP BY
            ms.IdObj1
    ) AS ms
    JOIN Server AS s
      ON s.Id = ms.IdObj1
    JOIN SB AS sb1 
      ON ms.sb_id_1 = sb1.Id
    LEFT JOIN SB AS sb2 
      ON  ms.sb_id_2 = sb2.Id
  ;