July 20, 2009 at 8:29 am
Table structure
CREATE TABLE [dbo].[tblAid](
[uidAidID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_tblAid_uidAidID] DEFAULT (newid()),
[intSendingNation] [int] NOT NULL,
[intReceivingNation] [int] NOT NULL,
[dtmAidDate] [datetime] NOT NULL,
[dblMoney] [float] NULL,
[dblSoldiers] [float] NULL,
[dblTech] [float] NULL,
[strAidMessage] [varchar](max) NULL,
CONSTRAINT [PK_tblAid] PRIMARY KEY CLUSTERED
(
[uidAidID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblNations](
[intCNNationID] [int] NOT NULL,
[strNationName] [varchar](max) NOT NULL,
[strNationRuler] [varchar](max) NOT NULL,
[uidResource1] [uniqueidentifier] NULL,
[uidResource2] [uniqueidentifier] NULL,
[DateTimeStamp] [datetime] NULL CONSTRAINT [DF_tblNations_DateTimeStamp] DEFAULT (getdate()),
[Active] [bit] NOT NULL CONSTRAINT [DF_tblNations_Active] DEFAULT ((1)),
[dtmNationCreated] [datetime] NOT NULL CONSTRAINT [[dbo]].[tblNations]]dtmNationCreatedDefault] DEFAULT ((0)),
CONSTRAINT [PK_tblNations] PRIMARY KEY CLUSTERED
(
[intCNNationID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblNationsInalliances](
[uidNationsInAlliancesID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_NationsInalliances_uidNationsInAlliancesID] DEFAULT (newid()),
[CNNationID] [int] NOT NULL,
[strAllianceName] [varchar](max) NOT NULL,
[dtmDateTimeStamp] [datetime] NOT NULL CONSTRAINT [DF_NationsInalliances_DateTimeStamp] DEFAULT (getdate()),
[intVersion] [int] NOT NULL CONSTRAINT [DF_tblNationsInalliances_intVersion] DEFAULT ((0)),
[intVersionLast] [int] NOT NULL CONSTRAINT [DF_tblNationsInalliances_intVersionLast] DEFAULT ((0)),
CONSTRAINT [PK_NationsInalliances] PRIMARY KEY CLUSTERED
(
[uidNationsInAlliancesID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[tblNationsInalliances] WITH CHECK ADD CONSTRAINT [FK_tblNationsInalliances_tblNations] FOREIGN KEY([CNNationID])
REFERENCES [dbo].[tblNations] ([intCNNationID])
GO
Sample data
INSERT INTO [dbo].[tblNations]
([intCNNationID]
,[strNationName]
,[strNationRuler]
,[Active]
,[dtmNationCreated])
VALUES
(165203
,'TestNation1'
,'TestRuler1'
,1
,'2009/01/01 00:01:01.000')
GO
INSERT INTO [dbo].[tblNations]
([intCNNationID]
,[strNationName]
,[strNationRuler]
,[Active]
,[dtmNationCreated])
VALUES
(165406
,'TestNation2'
,'TestRuler2'
,1
,'2009/03/01 00:01:01.000')
GO
INSERT INTO [dbo].[tblNationsInalliances]
([CNNationID]
,[strAllianceName]
,[intVersionLast])
VALUES
(165203
,'New Pacific Order'
,0)
GO
INSERT INTO [dbo].[tblNationsInalliances]
([CNNationID]
,[strAllianceName]
,[intVersionLast])
VALUES
(165406
,'New Pacific Order'
,0)
GO
INSERT INTO [dbo].[tblAid]
([intSendingNation]
,[intReceivingNation]
,[dtmAidDate]
,[dblMoney]
,[dblSoldiers]
,[dblTech]
,[strAidMessage])
VALUES
(165203
,165406
,'2009/07/10 14:23:00.000'
,3000000
,2000
,50
,'Test 1')
GO
INSERT INTO [dbo].[tblAid]
([intSendingNation]
,[intReceivingNation]
,[dtmAidDate]
,[dblMoney]
,[dblSoldiers]
,[dblTech]
,[strAidMessage])
VALUES
(165406
,165203
,'2009/07/20 14:23:00.000'
,1000000
,0
,0
,'Test 2')
GO
Query I have issues with:
SELECT DISTINCT tblAid.dtmAidDate, tblAid.intSendingNation, tblSendingNations.strNationRuler as strSendingNationRuler, tblSendingNations.strNationName as strSendingNationName, tblSendingNationsInAlliance.strAllianceName as strSendingAllianceName, tblAid.dblMoney, tblAid.dblSoldiers, tblAid.dblTech, tblAid.intReceivingNation, tblReceivingNations.strNationRuler AS strReceivingNationRuler, tblReceivingNations.strNationName as strReceivingNationName, tblReceivingNationsInAlliance.strAllianceName as strReceivingAllianceName
FROM tblAID (NOLOCK)
INNER JOIN tblNations tblReceivingNations ON tblAID.intReceivingNation = tblReceivingNations.intCNNationID
INNER JOIN tblNations tblSendingNations ON tblAid.intSendingNation = tblSendingNations.intCNNationID
INNER JOIN tblNationsInAlliances tblReceivingNationsInAlliance ON tblAID.intReceivingNation = tblReceivingNationsInAlliance.CNNationID
INNER JOIN tblNationsInAlliances tblSendingNationsInAlliance ON tblAID.intSendingNation = tblSendingNationsInAlliance.CNNationID
WHERE tblAid.dtmAIDDate > convert(datetime, '07/20/2009 00:00AM') AND (intSendingNation IN
(SELECT CNNationID FROM tblNationsInAlliances WHERE ([strAllianceName] = 'New Pacific Order')))
OR (intReceivingNation IN
(SELECT CNNationID FROM tblNationsInAlliances WHERE ([strAllianceName] = 'New Pacific Order')))
ORDER BY dtmAIDDate DESC
This query is supposed to only return 1 row of test data, namely the one that has a dtmAIDDate after July 20th 2009 0:00AM, but it returns the older data as well. I'm obviously doing something wrong, but I can't figure out what. Any help is appreciated.
July 20, 2009 at 8:35 am
Actually.. nevermind... I figured it out.
The correct query is:
SELECT DISTINCT tblAid.dtmAidDate, tblAid.intSendingNation, tblSendingNations.strNationRuler as strSendingNationRuler, tblSendingNations.strNationName as strSendingNationName, tblSendingNationsInAlliance.strAllianceName as strSendingAllianceName, tblAid.dblMoney, tblAid.dblSoldiers, tblAid.dblTech, tblAid.intReceivingNation, tblReceivingNations.strNationRuler AS strReceivingNationRuler, tblReceivingNations.strNationName as strReceivingNationName, tblReceivingNationsInAlliance.strAllianceName as strReceivingAllianceName
FROM tblAID (NOLOCK)
INNER JOIN tblNations tblReceivingNations ON tblAID.intReceivingNation = tblReceivingNations.intCNNationID
INNER JOIN tblNations tblSendingNations ON tblAid.intSendingNation = tblSendingNations.intCNNationID
INNER JOIN tblNationsInAlliances tblReceivingNationsInAlliance ON tblAID.intReceivingNation = tblReceivingNationsInAlliance.CNNationID
INNER JOIN tblNationsInAlliances tblSendingNationsInAlliance ON tblAID.intSendingNation = tblSendingNationsInAlliance.CNNationID
WHERE tblAid.dtmAIDDate > convert(datetime, '2009-07-20 00:00:00.000') AND ((intSendingNation IN
(SELECT CNNationID FROM tblNationsInAlliances WHERE ([strAllianceName] = 'New Pacific Order')))
OR (intReceivingNation IN
(SELECT CNNationID FROM tblNationsInAlliances WHERE ([strAllianceName] = 'New Pacific Order'))))
ORDER BY dtmAIDDate DESC
Was missing some ( ) 😛
Please lock topic
July 20, 2009 at 8:42 am
Thanks for the update.
I won't lock the topic, as someone might see something you could do better, but it's good to know what fixed it for you.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply