[B][players][/B]
id team_id name born
3 5 Peter 1980-07-07
4 6 Peter 1980-07-07
5 7 Peter 1980-07-07
[B][teams][/B]
id tour_id name
5 2 SIK
6 3 SIK3
7 4 SIK2
[B][tournaments][/B]
id name start_date
2 Test1 2008-10-11
3 Test2 2008-10-20
4 Test3 2008-11-12
Önskat resultat vid sökning på "Peter"
Id Name Team Born Count
5 Peter SIK2 1980-07-07 3
Vill alltså leta reda på alla som heter "Peter", gruppera dessa på födelsedag (born) samt hämta ut name från tabellen team där team ingår i den turnering (tournament) som har nyast start_date.
Har försökt lite men inte lyckats helt:
SELECT
max(p.id) as id,
p.name,
p.born,
max(t.name) as team,
count(p.id) as count
FROM players p
INNER JOIN teams t ON t.id=p.team_id
INNER JOIN tournaments tt ON tt.id=t.tour_id
WHERE p.name LIKE '%peter%'
GROUP BY p.name, p.born
ORDER BY p.name
create view myview
as
select
p.id as playerid,
p.born as playerborn,
p.name as playername,
t.name as teamname,
s.start_date as tournamentstart,
( select MAX(start_date) from tournaments s2
inner join teams t2 on s2.id = t2.tour_id
inner join players p2 on t2.id = p2.team_id
where p2.name = p.name ) as maxdate
from
players p inner join teams t
on
p.team_id = t.id
inner join tournaments s
on
t.tour_id = s.id
select
( select max(playerid) from myview where playername = v.playername ),
v.playerborn,
v.playername,
( select max(maxdate) from myview where playername = v.playername ),
( select teamname from myview where playername = v.playername and maxdate = tournamentstart ),
COUNT(*)
from
myview v
where
v.playername like '%peter%'
group by
v.playerborn,
v.playername
mja... En bit på väg i alla fall.
Om det finns en som heter "Peter O" och en som heter "Peter P" och man söker på "Peter" ska det bli två resultat, med nuvarande fråga blir det endast ett.
