LEFT JOIN but take only one row from right side

Context:

I have two tables: ks__dokument and ks_pz. It’s one-to-many relation where records from ks__dokument may have multiple records assigned from ks_pz.

Goal:

I want to show every row from ks__dokument and every row from ks__dokument must be shown only once.

What I tried:

Here is query I tried:

SELECT DISTINCT ks_id, * FROM ks__dokument AS dok1 
LEFT JOIN ks_pz ON ks_id = kp_ksid

But it still shows duplicates.

EDITS

• That ORDER BY and WHERE was unnecessary.
• I dont need DISTINCT, it’s just what I tried.

STRUCTURE OF TABLES

ks__dokument structure:

| ks_id | X | X | X | X | X | X |

ks_pz:

| kp_id | kp_ksid | X | X | X |

‘X’ are unimportant columns. kp_ksid is foreign key for ks__dokument.

Answer

Use OUTER APPLY:

SELECT dok1.*, k2.*
FROM ks__dokument dok1 OUTER APPLY
     (SELECT TOP (1) *
      FROM ks_pz 
      WHERE ks_id = kp_ksid
      ) k2
WHERE ks_usuniety = 0 AND
      ks_data_otrzymania >= '2020-08-31'
ORDER BY ks_rok, ks_nr ASC;

Normally, there would be an ORDER BY in the subquery to specify which row to return.

The structure of your question makes it impossible to know if the ORDER BY should be in the subquery or in the outer query — and the same for the WHERE conditions.

You really need to specify the tables where columns are coming from.