Creating my first view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Creating my first view

Author

Topic

Minterta
Starting Member

4 Posts

Posted - 2012-06-19 : 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
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-06-19 : 11:25:23

Without all of the underlying table structures and sample data, it is hard to try and help.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-19 : 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 - 2012-06-20 : 07:38:39

[code]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);
[/code]

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 - 2012-06-20 : 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

Minterta
Starting Member

4 Posts

Posted - 2012-06-20 : 14:19:49

SUM(DISTINCT(st.amount) does the trick, thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-20 : 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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources