Improve reduce functions of SQLite3 request

[Peter Otten]

Steffen Mutter wrote:

> Dennis Lee Bieber wrote:
>> Untested:
>>
>> SELECT DISTINCT * from
>> (select homenr as nr, home as club FROM Runde20122013
>> WHERE place="karlsruhe"
>> UNION SELECT guestnr as nr, guest as club FROM 20122013
>> WHERE place="karlsruhe")
>> limit 10
> 
> Hi Dennis,
> 
> here the output of your suggested solution:
> SELECT DISTINCT * FROM (
>   SELECT HeimNr as nr, Heim as club FROM Runde20122013
>     WHERE kreis ="karlsruhe"
> UNION
>   SELECT GastNr as nr, gast as club FROM Runde20122013
>     WHERE kreis ="karlsruhe") LIMIT 10;
> 
> 359|TV Calmbach
> 21101|SG Heidel/Helm
> 21236|JSG Neuth/Büch
> 23108|TG Eggenstein
> 23108|TGEggenstein 2 <-
> 23109|TV Ettlingenw
> 23109|TV Ettlingenw 2 <-
> 23112|TSV Jöhlingen
> 23112|TSV Jöhlingen 2 <-
> 23112|TSV Jöhlingen 3 <-
> 
> Still not like what I'm looking for.
> Maybe I should iterate through the list, pick out the nr and look for
> the club stick it to a new list and leave out those ones, with the longe
> r club name...

How about

SELECT nr, min(club) FROM (
  SELECT HeimNr as nr, Heim as club FROM Runde20122013
    WHERE kreis ="karlsruhe"
UNION
  SELECT GastNr as nr, gast as club FROM Runde20122013
    WHERE kreis ="karlsruhe")
GROUP BY nr;

However, I'm smelling a data normalization issue. It looks like you are 
interested in the club, but that there may be multiple teams per club and 
you are trying to derive the club from the team name. If that's the case you 
should consider a database layout with tables similar to the following (* to 
mark primary keys)

matches
-------
matchID*, hometeamID, guestteamID, ...

teams
-----
teamID*, clubID, teamname, ...

clubs
-----
clubID*, clubname, ...

With such a layout you could get all clubs with (untested, of course)

SELECT clubs.clubId, clubs.clubname FROM (
    SELECT hometeamID as teamID from matches
UNION
    SELECT guestteamID as teamID from matches) as participants
INNER JOIN teams on teams.teamID = participants.teamID
INNER JOIN clubs on teams.clubID = clubs.teamID;

I hope you can make sense of it...