Search code examples
sqlselectnestedfilemaker

Combining two nested SELECT with DISTINCT?

How do I look up DISTINCT values of one table, look for each name in another table and get both the values and their names as a result?

The Beleg table looks like this:

SELECT DISTINCT Ursprungskonto FROM Beleg
WHERE YEAR ( Valuta ) = 2016

gets me:

1000
1210
1220
1230

For each of these values, I need to lookup its name:

SELECT Name FROM Geldkonto
WHERE Kontonr = 1000

results in:

Kasse

At the end of the query, I need to have this result:

1000    Kasse
1210    OneBankName
1220    AnotherBankName
1230    YABN

I'm using SQL-92 (Filemaker).

Thanks a lot for any help!

Solution

  • You could try sub-query:

    SELECT Kontonr , Name FROM Geldkonto
WHERE Kontonr in (SELECT DISTINCT Ursprungskonto FROM Beleg
WHERE YEAR ( Valuta ) = 2016)