none
How to select rows from table shippolicyh that have related one row only one trxfootersafe ? RRS feed

  • Question

  • Problem

    How to select rows from table shippolicyh that have related one row only on table trxfootersafe ?

    meaning to modify query that get all policycode on shippolicyh that have one row only related on 

    table trxfootersafe .

    the following query not give me result 

    so that what I change .

    SELECT
       BranchCode,PolicyCode,PolicyDate,EgyPolicyNum
    FROM
        SHIPPolicyH H
    WHERE
        EXISTS (
            SELECT
                COUNT (*)
            FROM
                TrxFooterSafe F
            WHERE
                SubLdgCode2 = H.PolicyCode AND BranchCode = H.BranchCode AND SubLdgCode1=H.Sender and BranchCode=1 and Debit > 0 and Credit=0 and TypeTrxSafeCode=-1
            GROUP BY
                SubLdgCode2,SubLdgCode1
            HAVING
                COUNT (*) =1
        )
    ORDER BY
        PolicyCode,
        BranchCode;
    

    Sample to result

    policycode 1211 must not show because it have two lines on trxfootersafe table .
    Shippolicyh
    BranchCode, PolicyCode EgyPolicyNum  Sender
    1             1211        3494939      12
    
    trxfootersafe
    serial  year    subldgcode1   subldgcode2  debit credit typetrxsafe
    50      2019        12           1211       0     90    -1
    55      2019        12           1211       90     0     1
    policycode 5555 must show because it have one lines only on trxfootersafe table .
    
    Shippolicyh
    BranchCode, PolicyCode EgyPolicyNum  Sender
    1             5555        3494939      12
    
    trxfootersafe
    serial  year    subldgcode1   subldgcode2  debit credit typetrxsafe
    70      2019        12           5555       90     0   -1
    
    but problem that query above show all policy code on shippolicyh table that have one line or two lines
     so that how to make it show one line related only
    meaning show policycode number 5555 because it have one line
    and not show policycode 1211 because it have two lines
    
    
     

    Monday, July 29, 2019 8:25 AM

Answers

  • Hi engahmedbarbary,

     

    Also please try to use WINDOW FUNCTION.

     
    ;WITH rs AS
    (
    	SELECT SubLdgCode2, COUNT(SubLdgCode2)over(partition by SubLdgCode2 ) AS [Counter]
    	FROM [dbo].[TrxFooterSafe]
    )
    SELECT * FROM [dbo].[SHIPPolicyH] AS p
    INNER JOIN rs ON p.policycode = rs.SubLdgCode2
    where [Counter]=1

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 30, 2019 3:13 AM

All replies

  • Please post sample data + desired result. Always state what version you are using.
    Please share us more detailed information about your requirement like your table structure  and your expected result and 
    some sample data. So that it will help us understand your issue more clearly. 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, July 29, 2019 8:41 AM
    Answerer
  • OK i attached sample here on this post 

    i added two policycode 

    first on that have number 321330819 is wrong because it have one line for debit

    and not have any line for credit 

    so that i need to make query get me the wrong policycode or with another meaning

    I need to get policy code not have credit value on table trxfootersafe

    second policy code that have number 321335156 is correct because is have two lines 

    one for credit and one for debit 

    sot that i need to design query or modify above that get policycode that have one line as debit on

    trxfootersafe table .

    so that every policycode on shippolicyh have two lines on table trxfootersafe 

    one for debit and another for credit 

    but i have  some policy code have one line as debit only 

    so that i need to design query or modify on first one get me policy code have debit only or policy code have one line only

    meaning query must show policy code 321330819  because it have one line only

    and not show second one because it is correct .

    how to design query show policy code have one line only

    Monday, July 29, 2019 10:51 AM
  • Sorry I cannot access the link you posted above.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, July 29, 2019 10:56 AM
    Answerer
  • so that how to attach my file to access data 
    Monday, July 29, 2019 12:33 PM
  • Monday, July 29, 2019 1:13 PM
  • Post your question in simple scripts with table DDL and insert into scripts here.

    Don't post a link to a file. 

    Monday, July 29, 2019 3:47 PM
    Moderator
  • Hi engahmedbarbary,

    Please try the following SQL:

    CREATE TABLE [dbo].[SHIPPolicyH](
    	[BranchCode] [int] NOT NULL,
    	[PolicyCode] [int] NOT NULL,
    	[PolicyDate] [date] NULL,
    	[PukNo] [int] NULL,
    	[ShippingCode] [int] NULL,
    	[Sender] [int] NULL,
    	[SenderTel] [nvarchar](50) NULL,
    	[SenderEmail] [nvarchar](50) NULL,
    	[SenderZoneCode] [int] NULL,
    	[SenderCityCode] [int] NULL,
    	[SenderAddress] [nvarchar](250) NULL,
    	[SendPerson] [nvarchar](150) NULL,
    	[Receive] [nvarchar](150) NULL,
    	[RecieveTel] [nvarchar](50) NULL,
    	[RecieveEmail] [nvarchar](150) NULL,
    	[RecieveZoneCode] [int] NULL,
    	[RecieveCityCode] [int] NULL,
    	[PostCode] [nvarchar](50) NULL,
    	[RecieveAddress] [nvarchar](150) NULL,
    	[RecievePerson] [nvarchar](150) NULL,
    	[PackgingCode] [int] NULL,
    	[Tall] [float] NULL,
    	[Width] [float] NULL,
    	[Height] [float] NULL,
    	[DimensionalWeight] [float] NULL,
    	[ActualWeight] [float] NULL,
    	[Weight] [float] NULL,
    	[Count] [int] NULL,
    	[PolicyDesc] [nvarchar](150) NULL,
    	[ByEgy] [bit] NULL CONSTRAINT [DF_SHIPPolicyH_ByEgy]  DEFAULT ((0)),
    	[ByAgent] [bit] NULL CONSTRAINT [DF_SHIPPolicyH_ByAgent]  DEFAULT ((0)),
    	[AgentCode] [int] NULL,
    	[EgyPolicyNum] [nvarchar](50) NULL,
    	[EmpDeliveryCode] [int] NULL,
    	[CustomsValue] [float] NULL,
    	[ApplyCOD] [bit] NULL,
    	[SameDay] [bit] NULL,
    	[NextDay] [bit] NULL,
    	[RetrunService] [bit] NULL,
    	[RetrunServiceValue] [nvarchar](50) NULL,
    	[CODCustomer] [float] NULL,
    	[ShippingCost] [float] NULL,
    	[TotalCOD] [float] NULL,
    	[ReciveCOD] [bit] NULL,
    	[ReciveValue] [float] NULL,
    	[ReciveCustmer] [bit] NULL,
    	[IsClosed] [bit] NULL,
    	[ListCode] [int] NULL,
    	[NotActive] [bit] NULL,
    	[Price] [float] NULL,
    	[Tax1] [float] NULL,
    	[Tax2] [float] NULL,
    	[Tax3] [float] NULL,
    	[Tax4] [float] NULL,
    	[NetTax] [float] NULL,
    	[NetPrice] [float] NULL,
    	[AgentPrice] [float] NULL,
    	[AgentTax1] [float] NULL,
    	[AgentTax2] [float] NULL,
    	[AgentTax3] [float] NULL,
    	[AgentTax4] [float] NULL,
    	[AgentNetTax] [float] NULL,
    	[AgentNetPrice] [float] NULL,
    	[CreateUserId] [nvarchar](50) NULL,
    	[CreateDateAndTime] [datetime] NULL,
    	[UserId] [nvarchar](50) NULL,
    	[DateAndTime] [datetime] NULL,
    	[AgentOtherValue] [float] NULL,
    	[otherValue] [float] NULL,
    	[ReciveCustomer] [bit] NULL,
    	[DeliveryValue] [float] NULL,
    	[EmpSalesCode] [int] NULL,
    	[IsEdit] [bit] NULL,
    	[EmpCollectionCode] [int] NULL,
    	[CollectionValue] [float] NULL,
    	[CalcManual] [bit] NULL,
    	[PolicyDate_Hijri] [nvarchar](50) NULL,
    	[CustomerRate] [float] NULL,
    	[AgentRate] [float] NULL,
     CONSTRAINT [PK_Policy] PRIMARY KEY CLUSTERED 
    (
    	[BranchCode] ASC,
    	[PolicyCode] 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
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[TrxFooterSafe](
    	[BranchCode] [int] NOT NULL,
    	[TypeTrxSafeCode] [int] NOT NULL,
    	[Year] [int] NOT NULL,
    	[Serial] [int] NOT NULL,
    	[TrxLine] [int] NOT NULL,
    	[BranchTo] [int] NULL CONSTRAINT [DF__TrxFooter__Branc__24885067]  DEFAULT ((0)),
    	[AccCode] [nvarchar](50) NULL,
    	[Debit] [float] NOT NULL,
    	[Credit] [float] NOT NULL,
    	[CurrencyCode] [int] NOT NULL,
    	[CurrencyRate] [float] NOT NULL,
    	[EqDebit] [float] NOT NULL,
    	[EqCredit] [float] NOT NULL,
    	[PaymentType] [int] NULL,
    	[DocNo] [nvarchar](50) NULL,
    	[Notes] [nvarchar](max) NULL,
    	[SafeCode] [int] NULL CONSTRAINT [DF_TrxFooterSafe_SafeCode]  DEFAULT ((1)),
    	[BankCode] [int] NULL CONSTRAINT [DF__TrxFooter__BankC__267098D9]  DEFAULT ((0)),
    	[BankName] [int] NULL CONSTRAINT [DF__TrxFooter__BankN__2764BD12]  DEFAULT ((0)),
    	[SubLdgNo] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__2858E14B]  DEFAULT ((0)),
    	[SubLdgBranch1] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__294D0584]  DEFAULT ((0)),
    	[SubLdgCodeType1] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__2A4129BD]  DEFAULT ((0)),
    	[SubLdgCode1] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__2B354DF6]  DEFAULT ((0)),
    	[SubLdgBranch2] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__2C29722F]  DEFAULT ((0)),
    	[SubLdgCodeType2] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__2D1D9668]  DEFAULT ((0)),
    	[SubLdgCode2] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__2E11BAA1]  DEFAULT ((0)),
    	[SubLdgBranch3] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__2F05DEDA]  DEFAULT ((0)),
    	[SubLdgCodeType3] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__2FFA0313]  DEFAULT ((0)),
    	[SubLdgCode3] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__30EE274C]  DEFAULT ((0)),
    	[SubLdgBranch4] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__31E24B85]  DEFAULT ((0)),
    	[SubLdgCodeType4] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__32D66FBE]  DEFAULT ((0)),
    	[SubLdgCode4] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__33CA93F7]  DEFAULT ((0)),
    	[SubLdgBranch5] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__34BEB830]  DEFAULT ((0)),
    	[SubLdgCodeType5] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__35B2DC69]  DEFAULT ((0)),
    	[SubLdgCode5] [int] NULL CONSTRAINT [DF__TrxFooter__SubLd__36A700A2]  DEFAULT ((0)),
    	[FDesc] [nvarchar](max) NULL,
    	[PaymentTypeId] [int] NULL,
    	[drawee] [nvarchar](50) NULL,
    	[Bankdrawee] [nvarchar](50) NULL,
    	[ImportTypeCode] [int] NULL,
    	[ImptYear] [int] NULL,
    	[ImptSerial] [int] NULL,
    	[IsHiddenRow] [bit] NOT NULL CONSTRAINT [DF__TrxFooter__IsHid__10D65D75]  DEFAULT ((0)),
    	[ChkType] [int] NULL,
    	[LastChkStatus] [int] NULL,
    	[CheckNo] [nvarchar](50) NULL,
    	[CreateUserId] [nvarchar](50) NULL,
    	[CreateDateAndTime] [datetime] NULL,
    	[UserId] [nvarchar](50) NULL,
    	[DateAndTime] [datetime] NULL,
    	[PaymentDate] [datetime] NULL,
    	[Rased] [numeric](18, 0) NULL,
    	[CheckDueDate] [date] NULL,
    	[CheckStutas] [int] NULL,
    	[InvoiceType] [int] NULL,
    	[InvoiceYear] [int] NULL,
    	[InvoiceSerial] [int] NULL,
    	[invoicedetails] [nvarchar](500) NULL,
    	[ValueRatio] [float] NULL,
    	[DebitRatio] [float] NULL,
    	[CreditRatio] [float] NULL,
    	[CheckDueDate_Hijri] [nvarchar](50) NULL,
    	[PaymentDate_Hijri] [nvarchar](50) NULL,
    	[PaymentSerial] [int] NULL,
    	[TaxType] [int] NULL,
    	[AmountClass] [int] NULL,
    	[ReciptNo] [nvarchar](50) NULL,
    	[ReciptDate] [smalldatetime] NULL,
    	[SuppCode] [int] NULL,
     CONSTRAINT [PK_NewTrxFooterSafe] PRIMARY KEY CLUSTERED 
    (
    	[BranchCode] ASC,
    	[TypeTrxSafeCode] ASC,
    	[Year] ASC,
    	[Serial] ASC,
    	[TrxLine] 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]
    
    GO
    INSERT [dbo].[SHIPPolicyH] ([BranchCode], [PolicyCode], [PolicyDate], [PukNo], [ShippingCode], [Sender], [SenderTel], [SenderEmail], [SenderZoneCode], [SenderCityCode], [SenderAddress], [SendPerson], [Receive], [RecieveTel], [RecieveEmail], [RecieveZoneCode], [RecieveCityCode], [PostCode], [RecieveAddress], [RecievePerson], [PackgingCode], [Tall], [Width], [Height], [DimensionalWeight], [ActualWeight], [Weight], [Count], [PolicyDesc], [ByEgy], [ByAgent], [AgentCode], [EgyPolicyNum], [EmpDeliveryCode], [CustomsValue], [ApplyCOD], [SameDay], [NextDay], [RetrunService], [RetrunServiceValue], [CODCustomer], [ShippingCost], [TotalCOD], [ReciveCOD], [ReciveValue], [ReciveCustmer], [IsClosed], [ListCode], [NotActive], [Price], [Tax1], [Tax2], [Tax3], [Tax4], [NetTax], [NetPrice], [AgentPrice], [AgentTax1], [AgentTax2], [AgentTax3], [AgentTax4], [AgentNetTax], [AgentNetPrice], [CreateUserId], [CreateDateAndTime], [UserId], [DateAndTime], [AgentOtherValue], [otherValue], [ReciveCustomer], [DeliveryValue], [EmpSalesCode], [IsEdit], [EmpCollectionCode], [CollectionValue], [CalcManual], [PolicyDate_Hijri], [CustomerRate], [AgentRate]) VALUES (1, 321330819, CAST(N'2019-03-26' AS Date), 1111, 2, 1281, N'01119668118', NULL, NULL, 1, N'????? ?????? - ?????? ?????', NULL, N'Mr. Wael Abo Omar', N'966560549879', NULL, 68, 180, N'MAKKAH', N'Makkah Al Mukarramah 24222 - 3155', NULL, 2, 0, 0, 0, 0, 0.5, 0.5, NULL, NULL, 0, 1, 1, N'3804973255', 4, 0, 0, 0, 0, 0, NULL, 0, 0, 0, NULL, NULL, NULL, NULL, 3, NULL, 344.02, 0, 34.41, 0, 0, 34.41, 378.42, 118.26, 21.87, 14.02, 0, 21.58, 57.48, 175.74, N'm.samir', CAST(N'2019-03-28 21:48:35.000' AS DateTime), N'admin', CAST(N'2019-07-18 10:45:05.000' AS DateTime), 0, 0, NULL, 2, NULL, 1, NULL, NULL, 0, NULL, 17.34, 17.34)
    INSERT [dbo].[SHIPPolicyH] ([BranchCode], [PolicyCode], [PolicyDate], [PukNo], [ShippingCode], [Sender], [SenderTel], [SenderEmail], [SenderZoneCode], [SenderCityCode], [SenderAddress], [SendPerson], [Receive], [RecieveTel], [RecieveEmail], [RecieveZoneCode], [RecieveCityCode], [PostCode], [RecieveAddress], [RecievePerson], [PackgingCode], [Tall], [Width], [Height], [DimensionalWeight], [ActualWeight], [Weight], [Count], [PolicyDesc], [ByEgy], [ByAgent], [AgentCode], [EgyPolicyNum], [EmpDeliveryCode], [CustomsValue], [ApplyCOD], [SameDay], [NextDay], [RetrunService], [RetrunServiceValue], [CODCustomer], [ShippingCost], [TotalCOD], [ReciveCOD], [ReciveValue], [ReciveCustmer], [IsClosed], [ListCode], [NotActive], [Price], [Tax1], [Tax2], [Tax3], [Tax4], [NetTax], [NetPrice], [AgentPrice], [AgentTax1], [AgentTax2], [AgentTax3], [AgentTax4], [AgentNetTax], [AgentNetPrice], [CreateUserId], [CreateDateAndTime], [UserId], [DateAndTime], [AgentOtherValue], [otherValue], [ReciveCustomer], [DeliveryValue], [EmpSalesCode], [IsEdit], [EmpCollectionCode], [CollectionValue], [CalcManual], [PolicyDate_Hijri], [CustomerRate], [AgentRate]) VALUES (1, 321335156, CAST(N'2019-07-28' AS Date), 66777, 2, 1331, NULL, NULL, NULL, 2, NULL, NULL, N'????? ????', N'3444333', NULL, 35, 7, NULL, NULL, NULL, 1, 1, 1, 0, 0, 1, 1, NULL, NULL, 0, 1, 3, N'322219999', 33, 0, 0, 0, 0, 0, NULL, 0, 0, 0, NULL, NULL, NULL, NULL, 14, NULL, 20, 1, 1, 0, 1, 2, 25, 577.27, 57.72, 63.5, 0, 97.79, 219.02, 796.29, N'admin', CAST(N'2019-07-28 05:59:16.000' AS DateTime), NULL, NULL, 0, 0, NULL, 0, NULL, NULL, 37, NULL, 0, NULL, 0, 16.66)
    INSERT [dbo].[TrxFooterSafe] ([BranchCode], [TypeTrxSafeCode], [Year], [Serial], [TrxLine], [BranchTo], [AccCode], [Debit], [Credit], [CurrencyCode], [CurrencyRate], [EqDebit], [EqCredit], [PaymentType], [DocNo], [Notes], [SafeCode], [BankCode], [BankName], [SubLdgNo], [SubLdgBranch1], [SubLdgCodeType1], [SubLdgCode1], [SubLdgBranch2], [SubLdgCodeType2], [SubLdgCode2], [SubLdgBranch3], [SubLdgCodeType3], [SubLdgCode3], [SubLdgBranch4], [SubLdgCodeType4], [SubLdgCode4], [SubLdgBranch5], [SubLdgCodeType5], [SubLdgCode5], [FDesc], [PaymentTypeId], [drawee], [Bankdrawee], [ImportTypeCode], [ImptYear], [ImptSerial], [IsHiddenRow], [ChkType], [LastChkStatus], [CheckNo], [CreateUserId], [CreateDateAndTime], [UserId], [DateAndTime], [PaymentDate], [Rased], [CheckDueDate], [CheckStutas], [InvoiceType], [InvoiceYear], [InvoiceSerial], [invoicedetails], [ValueRatio], [DebitRatio], [CreditRatio], [CheckDueDate_Hijri], [PaymentDate_Hijri], [PaymentSerial], [TaxType], [AmountClass], [ReciptNo], [ReciptDate], [SuppCode]) VALUES (1, -1, 2019, 41077, 1, 0, N'            437', 378.42, 0, 1, 1, 378.42, 0, 0, N'', N'SAUDI ARABIA-DHL- 3804973255', 0, 0, 0, 1, 1, 1, 1281, 1, 11, 321330819, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'SAUDI ARABIA-DHL- 3804973255', 0, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
    INSERT [dbo].[TrxFooterSafe] ([BranchCode], [TypeTrxSafeCode], [Year], [Serial], [TrxLine], [BranchTo], [AccCode], [Debit], [Credit], [CurrencyCode], [CurrencyRate], [EqDebit], [EqCredit], [PaymentType], [DocNo], [Notes], [SafeCode], [BankCode], [BankName], [SubLdgNo], [SubLdgBranch1], [SubLdgCodeType1], [SubLdgCode1], [SubLdgBranch2], [SubLdgCodeType2], [SubLdgCode2], [SubLdgBranch3], [SubLdgCodeType3], [SubLdgCode3], [SubLdgBranch4], [SubLdgCodeType4], [SubLdgCode4], [SubLdgBranch5], [SubLdgCodeType5], [SubLdgCode5], [FDesc], [PaymentTypeId], [drawee], [Bankdrawee], [ImportTypeCode], [ImptYear], [ImptSerial], [IsHiddenRow], [ChkType], [LastChkStatus], [CheckNo], [CreateUserId], [CreateDateAndTime], [UserId], [DateAndTime], [PaymentDate], [Rased], [CheckDueDate], [CheckStutas], [InvoiceType], [InvoiceYear], [InvoiceSerial], [invoicedetails], [ValueRatio], [DebitRatio], [CreditRatio], [CheckDueDate_Hijri], [PaymentDate_Hijri], [PaymentSerial], [TaxType], [AmountClass], [ReciptNo], [ReciptDate], [SuppCode]) VALUES (1, -1, 2019, 41085, 1, 0, N'           1225', 25, 0, 1, 1, 25, 0, 0, N'', N'ANGUILLA-Aramex- ', 0, 0, 0, 1, 1, 1, 1331, 1, 11, 321335156, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'ANGUILLA-Aramex- ', 0, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
    INSERT [dbo].[TrxFooterSafe] ([BranchCode], [TypeTrxSafeCode], [Year], [Serial], [TrxLine], [BranchTo], [AccCode], [Debit], [Credit], [CurrencyCode], [CurrencyRate], [EqDebit], [EqCredit], [PaymentType], [DocNo], [Notes], [SafeCode], [BankCode], [BankName], [SubLdgNo], [SubLdgBranch1], [SubLdgCodeType1], [SubLdgCode1], [SubLdgBranch2], [SubLdgCodeType2], [SubLdgCode2], [SubLdgBranch3], [SubLdgCodeType3], [SubLdgCode3], [SubLdgBranch4], [SubLdgCodeType4], [SubLdgCode4], [SubLdgBranch5], [SubLdgCodeType5], [SubLdgCode5], [FDesc], [PaymentTypeId], [drawee], [Bankdrawee], [ImportTypeCode], [ImptYear], [ImptSerial], [IsHiddenRow], [ChkType], [LastChkStatus], [CheckNo], [CreateUserId], [CreateDateAndTime], [UserId], [DateAndTime], [PaymentDate], [Rased], [CheckDueDate], [CheckStutas], [InvoiceType], [InvoiceYear], [InvoiceSerial], [invoicedetails], [ValueRatio], [DebitRatio], [CreditRatio], [CheckDueDate_Hijri], [PaymentDate_Hijri], [PaymentSerial], [TaxType], [AmountClass], [ReciptNo], [ReciptDate], [SuppCode]) VALUES (1, 1, 2019, 438, 1, 1, N'           1209', 0, 10, 1, 1, 0, 10, NULL, N'6865', NULL, 1, 0, 0, 2, 1, 1, 1331, 1, 11, 321335156, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, NULL, N'admin', CAST(N'2019-07-28 06:16:57.000' AS DateTime), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
    
    ;WITH rs AS
    (
    	SELECT SubLdgCode2, COUNT(SubLdgCode2) AS [Counter]
    	FROM [dbo].[TrxFooterSafe]
    	GROUP BY SubLdgCode2
    	HAVING COUNT(SubLdgCode2) = 1
    )
    SELECT * FROM [dbo].[SHIPPolicyH] AS p
    	INNER JOIN rs ON p.policycode = rs.SubLdgCode2;
    

    Monday, July 29, 2019 5:27 PM
  • Hi engahmedbarbary,

     

    Also please try to use WINDOW FUNCTION.

     
    ;WITH rs AS
    (
    	SELECT SubLdgCode2, COUNT(SubLdgCode2)over(partition by SubLdgCode2 ) AS [Counter]
    	FROM [dbo].[TrxFooterSafe]
    )
    SELECT * FROM [dbo].[SHIPPolicyH] AS p
    INNER JOIN rs ON p.policycode = rs.SubLdgCode2
    where [Counter]=1

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 30, 2019 3:13 AM