Få fram vinnarna...

Jag har 4 tabeller som innehåller följande:

Contest
=========================
Identifier | Name | EndDate | ....

ContestItem
=========================
Identifier | ContestIdentifier | UserIdentifier | ...

Vote
=========================
Identifier | ContestItemIdentifier | ....

User
==========================
Identifier | UserName | ...

Contest innehåller tävlingens namn och slutdatum. ContestItem innehåller ett bidrag som är med i tävlingen och har en referens till själva tävlingen genom contestIdentifier. Till sist så har jag Vote tabellen som innehåller alla röster på ett bidrag, den innehåller en referens till contestItem.

Det jag nu skulle vilja är att plocka fram vinnarna för de senaste 4 tävlingarna, alltså där slutdatum har passerats. Det hade även varit kul att få antalet röster på ett bidrag och totala antalet röster i den tävlingen.

Typ något sånt här:

ContestName | SlutDatum | ContestItemName | User        | Röster | RösterTotalt
==========================================================================
Stolar      | 2008-03-01| MinStol         | Kalle Anka  | 10     | 100
Bord        | 2008-02-01| Kaya            | Erik        | 23     | 58
Tavlor      | 2008-01-01| Solnedgång      | Nisse       | 45     | 200
...

Är det någon som vet hur man skulle kunna skriva en SQL sats för att få fram all den informationen? Det skulle ju gå att lösa genom att ställa flera följdfrågor men helst så skulle ja vilja ha en SQL fråga.

- M

Vad händer om flera ContestItems fått lika många röster? Vem är vinnaren då?

Misstänker att alla blir vinnare så fall...

- M

Du kanske kan utgå ifrån följande:

select c.name
     , c.enddate
     , i.name
     , u.username
     , count(*)
     , ( select count(*) 
           from contest cc
         inner
           join contestitem ii
             on cc.identifier = ii.contestidentifier
         inner
           join vote vv
             on ii.identifier = vv.contestitemidentifier
          where cc.identifier = c.identifier )
  from contest c
inner
  join contestitem i
    on c.identifier = i.contestidentifier
inner
  join user u
    on i.useridentifier = u.identifier
inner
  join vote v
    on i.identifier = v.contestidentifier
 where c.enddate < getdate()
group
    by c.name
     , c.enddate
     , i.name
     , u.username
having count(*)
     = ( select max(t.votes)
           from ( select count(*) as votes
                    from vote vv
                  inner
                    join contestitem ii
                      on vv.contestitemidentifier = ii.identifier
                   where ii.contestidentifier = i.contestidentifier
                  group
                      by ii.identifier ) t )
order
    by c.enddate

Skit.. Det hade jag ju aldrig kommit fram till :). Skall testa det och se hur det blir. Tack så mycket.

- M