StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POView help. Pulling data from 3 tables
text
Body
copied!<p>Okay, I need some major help with this subject. This is what I need the view to do. It needs to take Sum of the DKP_Change Column in the Attendance table</p> <pre><code> SELECT SUM(a.DKP_Change) FROM Attendance AS a GROUP BY Name </code></pre> <p>add the value of the initial DKP from the characters table</p> <pre><code> SELECT b.Inital_DKP FROM Characters AS b GROUP BY Name </code></pre> <p>Subtract the sum of the raid drops tabe cost</p> <pre><code> SELECT SUM(c.Cost) FROM Raid_Drops AS c GROUP BY Name </code></pre> <p>I'm entirely new to the idea of VIEWS and i'm not sure where to begin with, the name of the view should be DKP, the columns should be Name and Total_DKP, where total dkp is calculated from teh above select statements.</p> <p>Here are the creates for all 3 tables.</p> <pre><code>CREATE TABLE `Attendance` ( `Date` date NOT NULL, `Name` varchar(20) NOT NULL, `Hours` int(11) NOT NULL, `Penalty` float NOT NULL, `Rank` set('Raider','Core','Elite') NOT NULL, `Rate` int(11) NOT NULL, `DKP_Change` float NOT NULL, `RecordNumber` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`RecordNumber`) ) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 CREATE TABLE `Characters` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(25) NOT NULL, `Class` varchar(25) NOT NULL, `Spec` varchar(25) NOT NULL, `Position` set('Healer','Tank','DPS') NOT NULL COMMENT 'Healer, Tank, or DPS', `Usable` set('Cloth','Mail','Plate') NOT NULL COMMENT 'Type of Usable Armor? Cloth, Mail, Or Plate', `Primary Stat` set('Agility','Strength','Intellect','Healer','Tank') NOT NULL COMMENT 'Used for Sorting Only(ie dps trinket with agility strength dps not eligible)', `Initial_DKP` int(11) NOT NULL COMMENT 'DKP given at the start of current tier.', `Total_DKP` int(11) NOT NULL COMMENT 'Huge Complicated Mess.', PRIMARY KEY (`ID`) ) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 CREATE TABLE `Raid_Drops` ( `Record Number` int(11) NOT NULL, `Date` date NOT NULL, `Name of Item` varchar(25) NOT NULL, `Item Slot` enum('Main Hand','Off Hand','Head','Neck','Shoulder','Back','Chest','Wrist','Hands','Waist','Legs','Feet','Ring 1','Ring 2','Trinket 1','Trinket 2') NOT NULL, `Player_Name` varchar(25) NOT NULL, `Cost` float NOT NULL, PRIMARY KEY (`Record Number`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload