مشکل در ارتباط و طراحی جداول و view

[MahmoodM30]

سلام به همه دوستان خسته نباشید من میخوام سه تا جدول داشته باشم مثلا به اسم

مشتریان
فیش های صادره
پرداختی ها

حالا من اومدم جداول رو به شکل زیر طراحی کردم که تو عکسه

150024
150025
150026

یعنی برای مشتری فیش صادر میشه حالا هر چند تا که بخواد

و تو قسمت پرداختی ها هم همون مشتری میاد مثلا سه تا پرداخت میکنه

تا اینجاش حله

ولی وقتی یه ویو درست میکنم

150027

میاد اطلاعات رو به صورت کلی نشون میده

مثلا تو جدول پرداختی ها من 3 تا واریزی برای محمود داشتم

تو ویو میاد برای هر فیش صادره 3 بار در نظر میگیره

150028

میخواستم بدون من این رو چجوری باید طراحی کنم تا به این مشکل بر نخورم

[Mahmoud.Afrad]

اسکریپت جداول را قرار بدید.

فکر میکنم با این طراحی به مشکل بخورید چون معلوم نیست هر پرداخت مربوط به چیه. ارتباط بین جداول هم درست نیست. توضیح بدید این فیشها و پرداخت ها چه رابطه ای دارند.

[MahmoodM30]

سلام ممنون از پاسخگوییتون

مثلا مشتری محمود براش 5 تا فیش صادر میشه دقیقا مثل صدور فاکتور ، هر فیش دارای یک مبلغ هست مثلا 5000 تومان حالا ممکنه برای یک نفر تو طول سال 2 بار یا بیشتر فیش صادر بشه حالا همه این فیش ها جمع میشه و میشه بدهی مشتری و همین فیش ها که صادر میشه تو جدول tblfish ذخیره میشه و توی جدول صدور فیش هم آیدی مشتری رو گذاشتم تا بشه از اون طریق کوئری نوشت و بدهی مشتریا رو در آورد

حالا این مشتریا میان و مثلا بدهیشون که 100 هزار تومانه (این 100 هزار ممکنه جمع 3 فیش صادر شده باشه) پرداخت کنن یبار 20 تومن میدن یبار 10 تمن یبار 70 تومن تا زمانی که دیگه تسویه بشن

حالا من میخوام با استفاده از این ویو یه کوئری بنویسم که بتونه واسم مبلغ قابل پرداخت ، مبلغ پرداخت شده ، مبلغ باقیمانده رو نشون بده برای هر مشتری رو نشون بده

فکر کنم منظورم رو رسوندم

ولی وقتی به این شکل جداولم رو تعریف و ویو میسازم با این مشکلات روبرو میشم و نمیتونم کاری بکنم

[MahmoodM30]

USE [test]
GO

/****** Object: Table [dbo].[tblfish] Script Date: 04/14/2019 00:08:32 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblfish](
[idfish] [int] IDENTITY(1,1) NOT NULL,
[idmoshtari] [int] NULL,
[numberfish] [int] NULL,
[jamekol] [int] NULL,
CONSTRAINT [PK_tblfish] PRIMARY KEY CLUSTERED
(
[idfish] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblfish] WITH CHECK ADD CONSTRAINT [FK_tblfish_tblmoshtari] FOREIGN KEY([idmoshtari])
REFERENCES [dbo].[tblmoshtari] ([idmoshtari])
GO

ALTER TABLE [dbo].[tblfish] CHECK CONSTRAINT [FK_tblfish_tblmoshtari]
GO



USE [test]
GO

/****** Object: Table [dbo].[tblmoshtari] Script Date: 04/14/2019 00:09:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblmoshtari](
[idmoshtari] [int] IDENTITY(1,1) NOT NULL,
[namemoshtari] [nvarchar](50) NULL,
CONSTRAINT [PK_tblmoshtari] PRIMARY KEY CLUSTERED
(
[idmoshtari] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO





USE [test]
GO

/****** Object: Table [dbo].[tblpardakhtfish] Script Date: 04/14/2019 00:09:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblpardakhtfish](
[idpardakht] [int] IDENTITY(1,1) NOT NULL,
[idmoshtari] [int] NULL,
[pardakhtkonande] [nchar](10) NULL,
[pardakhti] [int] NULL,
CONSTRAINT [PK_tblpardakhtfish] PRIMARY KEY CLUSTERED
(
[idpardakht] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblpardakhtfish] WITH CHECK ADD CONSTRAINT [FK_tblpardakhtfish_tblmoshtari] FOREIGN KEY([idmoshtari])
REFERENCES [dbo].[tblmoshtari] ([idmoshtari])
GO

ALTER TABLE [dbo].[tblpardakhtfish] CHECK CONSTRAINT [FK_tblpardakhtfish_tblmoshtari]
GO








USE [test]
GO

/****** Object: View [dbo].[View_1] Script Date: 04/14/2019 00:09:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[View_1]
AS
SELECT dbo.tblfish.idfish, dbo.tblfish.idmoshtari, dbo.tblfish.numberfish, dbo.tblfish.jamekol, dbo.tblpardakhtfish.pardakhtkonande, dbo.tblpardakhtfish.pardakhti,
dbo.tblpardakhtfish.idpardakht, dbo.tblmoshtari.namemoshtari
FROM dbo.tblfish INNER JOIN
dbo.tblmoshtari ON dbo.tblfish.idmoshtari = dbo.tblmoshtari.idmoshtari INNER JOIN
dbo.tblpardakhtfish ON dbo.tblmoshtari.idmoshtari = dbo.tblpardakhtfish.idmoshtari

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[41] 4[20] 2[4] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1[42] 2[22] 3) )"
End
Begin PaneConfiguration = 3
NumPanes = 3
Configuration = "(H (4 [30] 2 [40] 3))"
End
Begin PaneConfiguration = 4
NumPanes = 2
Configuration = "(H (1 [56] 3))"
End
Begin PaneConfiguration = 5
NumPanes = 2
Configuration = "(H (2 [66] 3))"
End
Begin PaneConfiguration = 6
NumPanes = 2
Configuration = "(H (4 [50] 3))"
End
Begin PaneConfiguration = 7
NumPanes = 1
Configuration = "(V (3))"
End
Begin PaneConfiguration = 8
NumPanes = 3
Configuration = "(H (1[56] 4[18] 2) )"
End
Begin PaneConfiguration = 9
NumPanes = 2
Configuration = "(H (1 [75] 4))"
End
Begin PaneConfiguration = 10
NumPanes = 2
Configuration = "(H (1[66] 2) )"
End
Begin PaneConfiguration = 11
NumPanes = 2
Configuration = "(H (4 [60] 2))"
End
Begin PaneConfiguration = 12
NumPanes = 1
Configuration = "(H (1) )"
End
Begin PaneConfiguration = 13
NumPanes = 1
Configuration = "(V (4))"
End
Begin PaneConfiguration = 14
NumPanes = 1
Configuration = "(V (2))"
End
ActivePaneConfig = 2
End
Begin DiagramPane =
Begin Origin =
Top = 0
Left = 0
End
Begin Tables =
Begin Table = "tblfish"
Begin Extent =
Top = 6
Left = 38
Bottom = 142
Right = 198
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "tblmoshtari"
Begin Extent =
Top = 30
Left = 289
Bottom = 119
Right = 449
End
DisplayFlags = 280
TopColumn = 0
End
Begin Table = "tblpardakhtfish"
Begin Extent =
Top = 0
Left = 556
Bottom = 145
Right = 729
End
DisplayFlags = 280
TopColumn = 0
End
End
End
Begin SQLPane =
End
Begin DataPane =
Begin ParameterDefaults = ""
End
Begin ColumnWidths = 9
Width = 284
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
Width = 1500
End
End
Begin CriteriaPane =
PaneHidden =
Begin ColumnWidths = 11
Column = 1440
Alias = 900
Table = 1170
Output = 720
Append = 1400
NewValue = 1170
SortType = 1350
SortOrder = 1410
GroupBy = 1350
Filter = 1350
Or = 1350
Or = 1350
Or = 1350
End
End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'View_1'
GO

[Mahmoud.Afrad]

وقتی بحث مجموع یا تعداد و از این قبیل توابع به ازای هر کد میشه بایست ذهنتون بره سمت گروهبندی و توابع sum, count ,...
SELECT
dbo.tblfish.idmoshtari,
dbo.tblmoshtari.namemoshtari,
SUM(dbo.tblfish.jamekol) AS ghabelePardakht,
SUM(dbo.tblpardakhtfish.pardakhti) AS JamePardakhtha,
SUM(dbo.tblfish.jamekol)-SUM(dbo.tblpardakhtfish.pardakhti) AS Baghimande
FROM dbo.tblfish
INNER JOIN dbo.tblmoshtari ON dbo.tblfish.idmoshtari = dbo.tblmoshtari.idmoshtari
INNER JOIN dbo.tblpardakhtfish ON dbo.tblmoshtari.idmoshtari = dbo.tblpardakhtfish.idmoshtari

GROUP BY
dbo.tblfish.idmoshtari ,
dbo.tblmoshtari.namemoshtari

[MahmoodM30]

سلام ممنون بابت توضیحات در مورد استفاده از Sum و ... میدونستم مششکل من تو نحوه محاسبه هستش داده های جداول من به شکل زیر هست

جدول صدور فیش که برای هر مشتری فیش صادر میشه و مبلغش توی فیلد JameKol قرار میگیره

150036

جدول پرداختی ها که مشتری میاد و مبالغی رو بابت این فیش های صادر شده پرداخت میکنه

150040

و حالا با اون کوئری که شما نوشتین باز محاسبات اشتباهه من توی تصویر مشخص کردم که چه اعدادی باید نشون داده بشه

150041

[Mahmoud.Afrad]

select
t.* ,
(t.ghabelePardakht - t.JamePardakhtha) AS Baghimande
from
(
SELECT
tblmoshtari.idmoshtari,
tblmoshtari.namemoshtari,
(select SUM(tblfish.jamekol) from tblfish where tblfish.idmoshtari = tblmoshtari.idmoshtari) AS ghabelePardakht ,
(select SUM(tblpardakhtfish.pardakhti) from tblpardakhtfish where tblpardakhtfish.idmoshtari = tblmoshtari.idmoshtari ) AS JamePardakhtha--,
FROM tblmoshtari
) t

[MahmoodM30]

سلام ممنون از راهنمایی خوبتون تونستم تا حدودی کار رو راه بندازم و یه سوال دیگه هم در این مورد داشتم میخوام مثلا فیلد های دیگه رو هم نشون بدم مثلا شماره فیش و پرداخت کننده و ... ولی نمیتونم ممنون میشم در این مورد هم راهنمایی کنید و ی هسوال دیگه هم این که من میخوام مقادیری رو که برای Null بر میگردونه 0 بشه من از ISNULL , COALESCE() استفاده میکنم جوابی نمیگیرم