[secag]

Hej, detta kanske inte är så avancerat för vissa men för mig är det så. Vill också bli bättre på SQL.

Jag har 3 tabeller. Dom heter users, items och member_items.

Såhär ser allting ut (.png):http://i.imgur.com/kZ7V3hN.png

Man kan äga flera vapen av samma typ också. Rader i member_items kan då se ut såhär:
:: Där ser ni att user_id kan äga 3 vapen av samma typ.

user_id    item_id
4             2
4             2
4             2
4             1
5             2

Om jag nu i en enda query vill få ut hur mycket en user har i attack och defence TILLSAMMANS.
Detta är mitt försök, längre kan jag inte komma.

SELECT user.id, SUM( user.attack + user.defence) AS total_power
FROM users
GROUP BY total_power

[linusoleander]

Så här kanske

SELECT users.id, SUM(users.attack + users.defence + items.extra_attack + items.extra_defence) AS total_power
INNER JOIN member_items ON users.id = member_items.user_id
INNER JOIN items ON member_items.item_id = items.id
FROM users
GROUP BY users.id

[secag]

Hmm, nu kommer bara de users som har vapen med. Går det att göra om frågan så även dom utan vapen syns i tabellen?

Lyckades lösa det genom några modifieringar, tack för grundstrukturen

PHP-kod:

SELECT members.member_id,
members.member_attack,
members.member_defence,
SUM(items.item_stat) + members.member_attack + members.member_defence
AS total_power 
FROM members
LEFT JOIN memberitems ON members.member_id = memberitems.memberitems_member
LEFT JOIN items ON memberitems.memberitems_item = items.item_id
GROUP BY members.member_id
ORDER BY total_power DESC 


[secag]

En följdfråga...
Vill hämta en persons rang(högst total_power = rang #1). Detta funkar men går det att göra på ett snyggare sätt?
Vill undvika att ha en statisk kolumn i usertable som håller reda på rangen.

PHP-kod:

function get_rank($member_id) {
        global $db;
        $i = 1;
        $q = $db->query("SELECT members.member_id,
members.member_attack,
members.member_defence,
SUM(items.item_stat) + members.member_attack + members.member_defence
AS total_power 
FROM members
LEFT JOIN memberitems ON members.member_id = memberitems.memberitems_member
LEFT JOIN items ON memberitems.memberitems_item = items.item_id
GROUP BY members.member_id
ORDER BY total_power");
        while($r = $q->fetch_array(MYSQLI_ASSOC)) {
            if($r["member_id"] == $member_id) {
                return $i;
            } else {
                $i++;
            }
        }
    } 


[Conny Westh]

Du har ingen PK i member_items och det gör att när du har fler items av samma typ så finns ingen möjlighet att identifiera enskilda tupler (förekomster) och hantera dem individuellt, annat än med rownumber (databasberoende). Du borde lägga till en egen PK i member_items så du kan identifiera enskilda tupler där.

Det verkar inte finnas någon RI (referencial Integrity) definierad på de Främmande nycklarna från member_items till users respektive items.


När du ställer en SQL-fråga här på forumet vore kanonbra om du lägger upp DDL-script för att skapa tabellerna och kanske lite insert-satser med exempeldata. Då det går snabbt för mig att lägga upp en exempeldatabas, och det går då snabbare att hjälpa dig, och du får troligen fler svar. Jag tycker det är kul att lösa SQL-problem så jag hjälper gärna till om jag kan och har tid.

[secag]

Okej, vad betyder tupler? Varför är det i detta fall nödvändigt att skilja alla member_items åt med PK?

Referencial Integrity har jag inte greppat, vill du förklara det för mig och vad som gäller i detta fall?

Och med DDL menar du typ att ja ska skriva upp alla tabellstrukturer? Har hört att man helst ska använda SQL-fiddle? Kommer med alla sannolikhet att fråga mer om SQL så ja vill gärna veta vad det är för något

[Conny Westh]

Tupler (plural) -> Är den matematiska benämningen på rader i en tabell.

Grader (plural) -> Den matematiska benämningen på kolumner i en tabell.

Referncial Integrity -> Ett sätt att låta databasen kontrollera att du bara använder giltiga Främmande nycklar (dvs värden som är Primärnyckel i en annan tabell, samt en del andra små trevliga saker som har med uppdatering och borttagning av primärnycklar som samtidigt är främmande nycklar i en annan tabell).

DDL-Script -> De SQL-satser du skriver som innehåller CREATE TABLE... dvs där du definierar strukturen. Detta kallas för Data Definition Language och är en delmängd av det gigantiska SQL-språket.

Varför behövs en primärnyckel i member_items-tabellen?
En viktig anledning till att använda primärnycklar i alla tabeller är att det är en del av den grundläggande databasteorin, och enligt andra och tredje normalformen måste varje tuple (dvs rad i tabellen) kunna identifieras på ett unikt sätt. ett vanligt sätt är att använda ett löpnummer 1,2,3,4... men man kan använda andra datatyper. Du har använd PK (primärnyckel) i tabellerna users och item för att kunna identifiera varje enskild tuple. Om du ska radera en enskild tuple i member_items-tabellen så går inte det därför att det inte går att identifiera en enstaka tuple, det bryter helt enkelt mot de mest elementära normaliseringsreglerna. När du tar bort ett värde idag med en enkel delete-sats så kommer alla värden med samma värden att raderas.

[secag]

Okej, ännu en del saker som är oklara men det fastnar väl inom kort...
Själv stötte jag på ett problem här som jag aldrig tidigare behövt göra. Jag vill radera alla rader i tabellen nedanför FÖRUTOM de 30 senaste raderna som baseras på message_time. Det är väl samma sak med message_id tror jag.

Mitt försök där jag fick stop. Här raderar jag alla förutom den senaste.

PHP-kod:

DELETE FROM shoutbox WHERE message_id NOT IN (SELECT MAX(message_id) FROM shoutbox); 


PHP-kod:

CREATE TABLE `shoutbox` (
 `message_id` int(11) NOT NULL AUTO_INCREMENT,
 `message_text` text NOT NULL,
 `message_time` int(11) NOT NULL,
 `message_author` int(11) NOT NULL,
 PRIMARY KEY (`message_id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 


[Conny Westh]

Du borde skriva något i stil med det här:

-- Kod för MySQL
DELETE 
FROM shoutbox 
WHERE message_id NOT IN 
(
    SELECT message_id 
    FROM shoutbox 
    ORDER BY message_id DESC
    LIMIT 0,30
);

-- Kod för MS SQL-Server
DELETE 
FROM shoutbox 
WHERE message_id NOT IN 
(
    SELECT TOP 30 message_id 
    FROM shoutbox 
    ORDER BY message_id DESC
);

När du använder max i din subquery så får du bara en tuple, men du ville ha 30.

[Conny Westh]

Efter att jag testkörde frågan såg jag att det var lite trubbel i MySQL, som inte uppträder i MSSQL, så här kommer en uppdaterad SQL-kod som jag har testkört utan data:

DELETE FROM `shoutbox`
WHERE message_id NOT IN 
(
  SELECT message_id
  FROM 
  (
    SELECT message_id
    FROM `shoutbox`
    ORDER BY message_id DESC
    LIMIT 30 -- Behåll så här många tupler
  ) foo
);

Man måste dock gå in i preferences och stänga av "safe mode" för det hindrar borttagning eller updates utan angivande av PK.