| Author |
 Topic  |
|
|
Minterta
Starting Member
4 Posts |
 Posted - 06/19/2012 : 09:35:23
|
Hello,
I'm new to SQL and I need some help to understanding it.
I want to see all my tile tables and every table that is linked to my tile table in a new table.
What I have now is almost what I want. But It gives me strange results. For an example the SUM(st.amount) doens't give the correct results and I dont see all my tiles, just an couple of them.
SELECT
t.idTile id,
t.xTile x,
t.yTile y,
t.createdTile created,
t.game_idGame game,
SUM(st.amount) skill,
SUM(atk.amount) healthLost,
ct.player_idPlayer player,
d.typeDiamond diamond,
d.expiredDiamond expireDiamond
FROM tile t
LEFT OUTER JOIN
losttile lt
ON
(t.idtile = lt.tile_idTile AND lt.dateLostTile = (SELECT MAX(dateLostTile) FROM losttile))
LEFT OUTER JOIN
capturedtile ct
ON
(t.idTile = ct.tile_idTile AND ct.dateCapturedTile = (SELECT MAX(dateCapturedTile) FROM capturedtile) AND lt.dateLostTile < (SELECT MAX(dateCapturedTile) FROM capturedtile))
LEFT OUTER JOIN
skilltile st
ON
(t.idTile = st.tile_idTile AND st.skillTileTimeStamp > ct.dateCapturedTile)
LEFT OUTER JOIN
attacktile atk
ON
(t.idTile = atk.tile_idTile AND atk.attackTileTimeStamp > ct.dateCapturedTile)
LEFT OUTER JOIN
diamond d
ON
(t.idTile = d.tile_idTile AND d.expiredDiamond >= NOW() AND d.operationalDiamond = true)
GROUP BY ct.player_idPlayer
Thanks |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 06/19/2012 : 14:14:04
|
make sure you follow link on Dons signature and post data in requested format for us to help you further!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
Minterta
Starting Member
4 Posts |
Posted - 06/20/2012 : 07:38:39
|
CREATE TABLE IF NOT EXISTS `attacktile` (
`idAttackTile` int(10) unsigned NOT NULL AUTO_INCREMENT,
`amount` int(11) NOT NULL,
`attackTileTimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`tile_idTile` int(10) unsigned NOT NULL,
`tile_idTile1` int(10) unsigned NOT NULL,
PRIMARY KEY (`idAttackTile`),
KEY `fk_attackTile_tile1` (`tile_idTile`),
KEY `fk_attackTile_tile2` (`tile_idTile1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;
INSERT INTO `attacktile` (`idAttackTile`, `amount`, `attackTileTimeStamp`, `tile_idTile`, `tile_idTile1`) VALUES
(1, 7, '2012-06-19 18:01:12', 16, 16),
(2, 4, '2012-06-19 13:00:09', 13, 1),
(3, 4, '2012-06-19 13:00:09', 1, 13),
(16, 10, '2013-06-20 11:14:19', 1, 16);
CREATE TABLE IF NOT EXISTS `capturedtile` (
`idcapturedTile` int(11) NOT NULL,
`dateCapturedTile` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`tile_idTile` int(10) unsigned NOT NULL,
`tile_idTile1` int(10) unsigned NOT NULL,
`player_idPlayer` int(10) unsigned NOT NULL,
PRIMARY KEY (`idcapturedTile`),
KEY `fk_capturedTile_tile1` (`tile_idTile`),
KEY `fk_capturedTile_player1` (`player_idPlayer`),
KEY `fk_capturedTile_tile2` (`tile_idTile1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `capturedtile` (`idcapturedTile`, `dateCapturedTile`, `tile_idTile`, `tile_idTile1`, `player_idPlayer`) VALUES
(0, '2002-06-19 11:07:31', 1, 13, 1),
(1, '2003-06-19 11:07:31', 13, 1, 1),
(2, '2002-06-18 22:00:00', 14, 14, 1),
(15, '2003-06-30 17:12:18', 16, 15, 1);
CREATE TABLE IF NOT EXISTS `diamond` (
`idDiamond` int(10) unsigned NOT NULL AUTO_INCREMENT,
`typeDiamond` int(11) NOT NULL DEFAULT '0',
`expiredDiamond` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`operationalDiamond` tinyint(1) NOT NULL,
`tile_idTile` int(10) unsigned NOT NULL,
`player_idPlayer` int(10) unsigned NOT NULL,
PRIMARY KEY (`idDiamond`),
KEY `fk_diamond_tile1` (`tile_idTile`),
KEY `fk_diamond_player1` (`player_idPlayer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
INSERT INTO `diamond` (`idDiamond`, `typeDiamond`, `expiredDiamond`, `operationalDiamond`, `tile_idTile`, `player_idPlayer`) VALUES
(2, 0, '2012-06-30 11:09:57', 1, 1, 1),
(4, 1, '2012-06-30 17:06:21', 1, 13, 1);
CREATE TABLE IF NOT EXISTS `losttile` (
`idlostTile` int(11) NOT NULL,
`dateLostTile` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`tile_idTile` int(10) unsigned NOT NULL,
`player_idPlayer` int(10) unsigned NOT NULL,
PRIMARY KEY (`idlostTile`),
KEY `fk_lostTile_tile1` (`tile_idTile`),
KEY `fk_lostTile_player1` (`player_idPlayer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `losttile` (`idlostTile`, `dateLostTile`, `tile_idTile`, `player_idPlayer`) VALUES
(0, '2012-06-19 00:16:42', 1, 1),
(1, '2012-06-19 00:16:42', 13, 1);
CREATE TABLE IF NOT EXISTS `player` (
`idPlayer` int(10) unsigned NOT NULL AUTO_INCREMENT,
`fidPlayer` varchar(45) NOT NULL,
`namePlayer` varchar(45) NOT NULL,
`nicknamePlayer` varchar(45) NOT NULL,
`createdPlayer` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`coinPlayer` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`idPlayer`),
UNIQUE KEY `fidPlayer_UNIQUE` (`fidPlayer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
INSERT INTO `player` (`idPlayer`, `fidPlayer`, `namePlayer`, `nicknamePlayer`, `createdPlayer`, `coinPlayer`) VALUES
(1, '100000491063425 ', 'Frank', 'Admin', '2012-06-18 23:50:58', 0),
(2, '1000029387', 'HELLO TEST', 'HELLO TEST', '2012-06-19 21:26:32', 0);
CREATE TABLE IF NOT EXISTS `skilltile` (
`idSkillTile` int(11) NOT NULL AUTO_INCREMENT,
`amount` int(11) NOT NULL DEFAULT '1',
`skillTileTimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`tile_idTile` int(10) unsigned NOT NULL,
PRIMARY KEY (`idSkillTile`),
KEY `fk_skillTile_tile1` (`tile_idTile`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
INSERT INTO `skilltile` (`idSkillTile`, `amount`, `skillTileTimeStamp`, `tile_idTile`) VALUES
(5, 1, '2012-06-19 16:58:47', 13),
(6, 1, '2012-06-19 17:09:08', 1),
(7, 10, '2012-06-19 17:11:03', 16),
(8, -1, '2012-06-19 17:31:10', 1);
CREATE TABLE IF NOT EXISTS `tile` (
`idTile` int(10) unsigned NOT NULL AUTO_INCREMENT,
`xTile` int(11) NOT NULL DEFAULT '0',
`yTile` int(11) NOT NULL DEFAULT '0',
`createdTile` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`game_idGame` int(10) unsigned NOT NULL,
PRIMARY KEY (`idTile`),
KEY `fk_tile_game1` (`game_idGame`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;
INSERT INTO `tile` (`idTile`, `xTile`, `yTile`, `createdTile`, `game_idGame`) VALUES
(1, 0, 0, '2012-06-18 23:52:54', 1),
(13, 1, 1, '2012-06-18 23:52:54', 1),
(14, 0, 0, '2012-06-19 13:10:35', 2),
(15, 0, 0, '2012-06-19 13:28:50', 1),
(16, 0, 0, '2012-06-19 13:32:41', 2);
There are some KEYS from other tables, so not sure if this is everything you need and some constraints I didn't post here.
|
|
|
|
Minterta
Starting Member
4 Posts |
Posted - 06/20/2012 : 07:44:59
|
I'm almost there, I just dont get why I get double the amount of IFNULL(SUM(st.amount), 0) skill and IFNULL(SUM(lost.amount), 0) healthLost then there is.
The code is slightly different then above. I figure out that ORDER BY is important for SUM(..) and that is why I didn't see all my TILES.
SELECT
t.idTile id,
t.xTile x,
t.yTile y,
t.createdTile created,
t.game_idGame game,
IFNULL(SUM(st.amount), 0) skill,
IFNULL(SUM(lost.amount), 0) healthLost,
IFNULL(ct.player_idPlayer, 0) player,
IFNULL(MAX(atk.attackTileTimeStamp), NULL) lastAttack
FROM tile t
LEFT JOIN
capturedtile ct
ON
(t.idTile = ct.tile_idTile AND ct.dateCapturedTile = (SELECT MAX(dateCapturedTile) FROM capturedtile))
LEFT JOIN
skilltile st
ON
(t.idTile = st.tile_idTile)
LEFT JOIN
attacktile lost
ON
(t.idTile = lost.tile_idTile AND lost.attackTileTimeStamp >= (SELECT MAX(dateCapturedTile) FROM capturedtile WHERE t.idTile = tile_idTile))
LEFT JOIN
attacktile atk
ON
(t.idTile = atk.tile_idTile1 AND atk.attackTileTimeStamp >= (SELECT MAX(dateCapturedTile) FROM capturedtile WHERE t.idTile = tile_idTile))
GROUP BY id
Is there a problem that I JOIN two the same tables? Because I want the latest date a tile attack a other tile, and how much a tile lost.
Expected Result:
ID X Y CREATED GAME SKILL HEALTHLOST PLAYER LASTATTACK
1 0 0 2012-06-19 01:52:54 1 0 28(19) 0 2012-06-19 15:00:09
13 1 1 2012-06-19 01:52:54 1 1 4 0 2012-06-19 15:00:09
14 0 0 2012-06-19 15:10:35 2 0 0 0 NULL
15 0 0 2012-06-19 15:28:50 1 0 0 0 NULL
16 0 0 2012-06-19 15:32:41 2 20(10) 14(7) 1 2013-06-20 13:14:19
Between (..) Is what I would expect but it gives me double the digits |
Edited by - Minterta on 06/20/2012 08:17:51 |
|
|
|
Minterta
Starting Member
4 Posts |
Posted - 06/20/2012 : 14:19:49
|
| SUM(DISTINCT(st.amount) does the trick, thanks. |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 06/20/2012 : 14:21:57
|
| Just a heads up, SQLTeam is a Microsoft SQL Server website, we don't handle MySQL questions. There's a MySQL forum over at http://dbforums.com/ |
|
|
| |
Topic |
|
|
|
Creating my first view
