mohsen-unique
چهارشنبه 29 آذر 1391, 13:29 عصر
با سلام
من سه تا جدول دارم که می خوام ازشون یه select بگیرم
جدولام به صورت زیر هستن
CREATE TABLE [dbo].[Agency](
[AgencyID] [int] IDENTITY(1,1) NOT NULL,
[AgencyName] [nvarchar](50) NOT NULL,
[AgencyEmail] [nvarchar](50) NOT NULL,
[AgencyPassword] [nvarchar](max) NOT NULL,
[AgencyDebt] [int] NOT NULL,
[IsActive] [int] NULL,
[AgencyTel] [nvarchar](50) NULL,
CONSTRAINT [PK_Agency] PRIMARY KEY CLUSTERED
(
[AgencyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Orders](
[Orderid] [bigint] IDENTITY(1,1) NOT NULL,
[Userid] [bigint] NOT NULL,
[Price] [nvarchar](max) NOT NULL,
[Date] [datetime] NOT NULL,
[TypeOfService] [nvarchar](max) NULL,
[Au] [nvarchar](max) NULL,
[IsConfirmed] [int] NULL,
[Get_ID] [nvarchar](max) NULL,
[url] [nvarchar](max) NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[Orderid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Users](
[Userid] [bigint] IDENTITY(1,1) NOT NULL,
[Username] [nvarchar](50) NULL,
[Password] [nvarchar](100) NULL,
[RegisterDate] [datetime] NOT NULL,
[ExpDate] [datetime] NULL,
[Email] [nvarchar](100) NULL,
[AgencyID] [int] NULL,
[Port] [int] NULL,
[IsActive] [int] NULL,
[MD5Pass] [nvarchar](100) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
کوری select رو هم اینطوری تعریف کردم
SELECT dbo.Users.Userid, dbo.Users.Username, dbo.Users.Password, dbo.Orders.TypeOfService, dbo.Orders.Price, dbo.Users.ExpDate, dbo.Users.Email,
dbo.Users.AgencyID
FROM dbo.Agency INNER JOIN
dbo.Users ON dbo.Agency.AgencyID = dbo.Users.AgencyID INNER JOIN
dbo.Orders ON dbo.Users.Userid = dbo.Orders.Userid
این مشکل رو با این کوری دارم که وقتی توی جدول orders چندتا ریکورد برای یک یوزر وجود داشته باشه به ازای هر رکوردی که اونجا هست یه رکورد در نتیجه اضافه می شه در صورتی که من می خوام فقط آخرین سفارش رو توی نتیجه نمایش بده
چیکار باید بکنم؟
من سه تا جدول دارم که می خوام ازشون یه select بگیرم
جدولام به صورت زیر هستن
CREATE TABLE [dbo].[Agency](
[AgencyID] [int] IDENTITY(1,1) NOT NULL,
[AgencyName] [nvarchar](50) NOT NULL,
[AgencyEmail] [nvarchar](50) NOT NULL,
[AgencyPassword] [nvarchar](max) NOT NULL,
[AgencyDebt] [int] NOT NULL,
[IsActive] [int] NULL,
[AgencyTel] [nvarchar](50) NULL,
CONSTRAINT [PK_Agency] PRIMARY KEY CLUSTERED
(
[AgencyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Orders](
[Orderid] [bigint] IDENTITY(1,1) NOT NULL,
[Userid] [bigint] NOT NULL,
[Price] [nvarchar](max) NOT NULL,
[Date] [datetime] NOT NULL,
[TypeOfService] [nvarchar](max) NULL,
[Au] [nvarchar](max) NULL,
[IsConfirmed] [int] NULL,
[Get_ID] [nvarchar](max) NULL,
[url] [nvarchar](max) NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[Orderid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[Users](
[Userid] [bigint] IDENTITY(1,1) NOT NULL,
[Username] [nvarchar](50) NULL,
[Password] [nvarchar](100) NULL,
[RegisterDate] [datetime] NOT NULL,
[ExpDate] [datetime] NULL,
[Email] [nvarchar](100) NULL,
[AgencyID] [int] NULL,
[Port] [int] NULL,
[IsActive] [int] NULL,
[MD5Pass] [nvarchar](100) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[Userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
کوری select رو هم اینطوری تعریف کردم
SELECT dbo.Users.Userid, dbo.Users.Username, dbo.Users.Password, dbo.Orders.TypeOfService, dbo.Orders.Price, dbo.Users.ExpDate, dbo.Users.Email,
dbo.Users.AgencyID
FROM dbo.Agency INNER JOIN
dbo.Users ON dbo.Agency.AgencyID = dbo.Users.AgencyID INNER JOIN
dbo.Orders ON dbo.Users.Userid = dbo.Orders.Userid
این مشکل رو با این کوری دارم که وقتی توی جدول orders چندتا ریکورد برای یک یوزر وجود داشته باشه به ازای هر رکوردی که اونجا هست یه رکورد در نتیجه اضافه می شه در صورتی که من می خوام فقط آخرین سفارش رو توی نتیجه نمایش بده
چیکار باید بکنم؟