locked
Query Twice Execution. RRS feed

  • Question

  • Hi,

    I execute  a joint query for 3 tables (Receipt, Items and Payment) where Receipt table is primary table While Items & Payment table have foreign keys to Receipt without any Primary Keys of their own. 

    The problem is when I try Fetching data the query executes twice for an ID where I got more then two rows In Items and Payment Tables.

    The Query is 

    SELECT     Receipt.*, Items.*, Payment.*
    FROM        Receipt INNER JOIN
                          Items ON Receipt.RID = Items.RID INNER JOIN
                          Payment ON Receipt.RID = Payment.RID

    Before You guys ask; why did I separate Payment table?

    It's because There might be Installments due to credits.


    Habib Ur Rehman

    Friday, April 12, 2019 2:19 AM

Answers

  • What we cannot do is read your mind nor see your screen. What does "ID" represent here? Is it the primary key of Receipt? And I will point out that your resultset has no particular order - which is likely a big problem since you join tables. 

    By joining in this fashion, your resultset does not represent any one "thing". Payments have no relationship to Items but the join effectively makes one. Say you have 1 row in receipt, 2 rows in Items, and 2 rows in Payment. How many rows do you expect in your resultset? Do you expect 2? That would be incorrect. Below is an example

    declare @receipt table (receipt_id smallint); 
    declare @items table (item_id smallint, receipt_id smallint);
    declare @payments table (pay_id smallint, receipt_id smallint, amt numeric(5,2)); 
                                                                               
    insert @receipt(receipt_id) values (1);
    insert @items(item_id, receipt_id) values(1, 1), (2, 1);
    insert @payments(pay_id, receipt_id, amt) values(5, 1, .10), (3, 1, 1.0);
                                                                               
    select * from @receipt as rt inner join @items as it on rt.receipt_id = it.receipt_id
    inner join @payments as py on rt.receipt_id = py.receipt_id ;

    And your next question is likely to be "how do I fix this?". No one can say since no one knows your goal. At this point, the only route forward is to query payments separately from items if you need to see the detail. 

    Sunday, April 14, 2019 11:53 AM

All replies

  • Probably because a relationship one-to many?

    For one receipt  you can have many items and many payments, am I right?


    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

    Sunday, April 14, 2019 4:23 AM
  • Hi,

    I execute  a joint query for 3 tables (Receipt, Items and Payment) where Receipt table is primary table While Items & Payment table have foreign keys to Receipt without any Primary Keys of their own. 

    The problem is when I try Fetching data the query executes twice for an ID where I got more then two rows In Items and Payment Tables.

    The Query is 

    SELECT     Receipt.*, Items.*, Payment.*
    FROM        Receipt INNER JOIN
                          Items ON Receipt.RID = Items.RID INNER JOIN
                          Payment ON Receipt.RID = Payment.RID

    Before You guys ask; why did I separate Payment table?

    It's because There might be Installments due to credits.


    Habib Ur Rehman

    yes

    it can be due to installments which means multiple payments against same receipt.

    If you can let us know what result you're after we may be able to suggest correct query for it

    For this, show some sample data from tables and then show your intended output


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Sunday, April 14, 2019 5:35 AM
  • What we cannot do is read your mind nor see your screen. What does "ID" represent here? Is it the primary key of Receipt? And I will point out that your resultset has no particular order - which is likely a big problem since you join tables. 

    By joining in this fashion, your resultset does not represent any one "thing". Payments have no relationship to Items but the join effectively makes one. Say you have 1 row in receipt, 2 rows in Items, and 2 rows in Payment. How many rows do you expect in your resultset? Do you expect 2? That would be incorrect. Below is an example

    declare @receipt table (receipt_id smallint); 
    declare @items table (item_id smallint, receipt_id smallint);
    declare @payments table (pay_id smallint, receipt_id smallint, amt numeric(5,2)); 
                                                                               
    insert @receipt(receipt_id) values (1);
    insert @items(item_id, receipt_id) values(1, 1), (2, 1);
    insert @payments(pay_id, receipt_id, amt) values(5, 1, .10), (3, 1, 1.0);
                                                                               
    select * from @receipt as rt inner join @items as it on rt.receipt_id = it.receipt_id
    inner join @payments as py on rt.receipt_id = py.receipt_id ;

    And your next question is likely to be "how do I fix this?". No one can say since no one knows your goal. At this point, the only route forward is to query payments separately from items if you need to see the detail. 

    Sunday, April 14, 2019 11:53 AM
  • Here is a sample database

    USE [Test]
    GO
    /****** Object:  Table [dbo].[Receipt]    Script Date: 04/19/2019 15:53:01 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Receipt](
    	[ID] [bigint] NOT NULL,
    	[InvoiceNo] [nvarchar](50) NOT NULL,
    	[InvoiceDate] [date] NOT NULL,
    	[ReceivedDate] [date] NOT NULL,
     CONSTRAINT [PK_Receipt] PRIMARY KEY CLUSTERED 
    (
    	[ID] 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
    INSERT [dbo].[Receipt] ([ID], [InvoiceNo], [InvoiceDate], [ReceivedDate]) VALUES (1, N'1', CAST(0x8F3F0B00 AS Date), CAST(0x8F3F0B00 AS Date))
    INSERT [dbo].[Receipt] ([ID], [InvoiceNo], [InvoiceDate], [ReceivedDate]) VALUES (2, N'2', CAST(0x8F3F0B00 AS Date), CAST(0x8F3F0B00 AS Date))
    /****** Object:  Table [dbo].[Payment]    Script Date: 04/19/2019 15:53:01 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[Payment](
    	[ID] [bigint] NOT NULL,
    	[Payment_No] [int] NOT NULL,
    	[TotalAmount] [decimal](18, 2) NOT NULL,
    	[Payment_Method] [varchar](50) NOT NULL,
    	[CashPaid] [decimal](18, 2) NULL,
    	[ChequePaid] [decimal](18, 2) NULL,
    	[Remaining_Credit] [decimal](18, 2) NULL,
    	[Receivers_Name] [varchar](50) NULL,
    	[ChequeNo] [int] NULL,
    	[ChequeDate] [date] NULL,
    	[BankName] [varchar](50) NULL,
    	[DeliveryDate] [date] NULL
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    INSERT [dbo].[Payment] ([ID], [Payment_No], [TotalAmount], [Payment_Method], [CashPaid], [ChequePaid], [Remaining_Credit], [Receivers_Name], [ChequeNo], [ChequeDate], [BankName], [DeliveryDate]) VALUES (1, 1, CAST(75.00 AS Decimal(18, 2)), N'Cash & Credit', CAST(50.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(25.00 AS Decimal(18, 2)), N'John', NULL, NULL, NULL, CAST(0x8F3F0B00 AS Date))
    INSERT [dbo].[Payment] ([ID], [Payment_No], [TotalAmount], [Payment_Method], [CashPaid], [ChequePaid], [Remaining_Credit], [Receivers_Name], [ChequeNo], [ChequeDate], [BankName], [DeliveryDate]) VALUES (1, 2, CAST(75.00 AS Decimal(18, 2)), N'Cash', CAST(25.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'John', NULL, NULL, NULL, CAST(0x8F3F0B00 AS Date))
    INSERT [dbo].[Payment] ([ID], [Payment_No], [TotalAmount], [Payment_Method], [CashPaid], [ChequePaid], [Remaining_Credit], [Receivers_Name], [ChequeNo], [ChequeDate], [BankName], [DeliveryDate]) VALUES (2, 1, CAST(101.74 AS Decimal(18, 2)), N'Cash, Cheque & Credit', CAST(20.00 AS Decimal(18, 2)), CAST(31.74 AS Decimal(18, 2)), CAST(50.00 AS Decimal(18, 2)), N'Kim', 7564, CAST(0x8F3F0B00 AS Date), N'xyz', CAST(0x8F3F0B00 AS Date))
    INSERT [dbo].[Payment] ([ID], [Payment_No], [TotalAmount], [Payment_Method], [CashPaid], [ChequePaid], [Remaining_Credit], [Receivers_Name], [ChequeNo], [ChequeDate], [BankName], [DeliveryDate]) VALUES (2, 2, CAST(101.74 AS Decimal(18, 2)), N'Cash & Credit', CAST(30.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(20.00 AS Decimal(18, 2)), N'Kim', NULL, NULL, NULL, CAST(0x8F3F0B00 AS Date))
    INSERT [dbo].[Payment] ([ID], [Payment_No], [TotalAmount], [Payment_Method], [CashPaid], [ChequePaid], [Remaining_Credit], [Receivers_Name], [ChequeNo], [ChequeDate], [BankName], [DeliveryDate]) VALUES (2, 3, CAST(101.74 AS Decimal(18, 2)), N'Cash', CAST(20.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), CAST(0.00 AS Decimal(18, 2)), N'Kim', NULL, NULL, NULL, CAST(0x8F3F0B00 AS Date))
    /****** Object:  Table [dbo].[Items]    Script Date: 04/19/2019 15:53:01 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Items](
    	[ID] [bigint] NOT NULL,
    	[ItemNo] [int] NOT NULL,
    	[Name] [nvarchar](100) NOT NULL,
    	[Price] [decimal](18, 2) NOT NULL
    ) ON [PRIMARY]
    GO
    INSERT [dbo].[Items] ([ID], [ItemNo], [Name], [Price]) VALUES (1, 1, N'abc', CAST(25.00 AS Decimal(18, 2)))
    INSERT [dbo].[Items] ([ID], [ItemNo], [Name], [Price]) VALUES (1, 2, N'def', CAST(20.00 AS Decimal(18, 2)))
    INSERT [dbo].[Items] ([ID], [ItemNo], [Name], [Price]) VALUES (1, 3, N'ghi', CAST(30.00 AS Decimal(18, 2)))
    INSERT [dbo].[Items] ([ID], [ItemNo], [Name], [Price]) VALUES (2, 1, N'qwe', CAST(55.99 AS Decimal(18, 2)))
    INSERT [dbo].[Items] ([ID], [ItemNo], [Name], [Price]) VALUES (2, 2, N'tyu', CAST(45.75 AS Decimal(18, 2)))
    /****** Object:  ForeignKey [FK_Items_Receipt]    Script Date: 04/19/2019 15:53:01 ******/
    ALTER TABLE [dbo].[Items]  WITH CHECK ADD  CONSTRAINT [FK_Items_Receipt] FOREIGN KEY([ID])
    REFERENCES [dbo].[Receipt] ([ID])
    GO
    ALTER TABLE [dbo].[Items] CHECK CONSTRAINT [FK_Items_Receipt]
    GO
    /****** Object:  ForeignKey [FK_Payment_Receipt]    Script Date: 04/19/2019 15:53:01 ******/
    ALTER TABLE [dbo].[Payment]  WITH CHECK ADD  CONSTRAINT [FK_Payment_Receipt] FOREIGN KEY([ID])
    REFERENCES [dbo].[Receipt] ([ID])
    GO
    ALTER TABLE [dbo].[Payment] CHECK CONSTRAINT [FK_Payment_Receipt]
    GO
    

    When I apply this query

    SELECT     dbo.Receipt.ID, dbo.Receipt.InvoiceNo, dbo.Receipt.InvoiceDate, dbo.Receipt.ReceivedDate, dbo.Items.ItemNo, dbo.Items.Name, dbo.Items.Price, dbo.Payment.Payment_No, 
                          dbo.Payment.TotalAmount, dbo.Payment.Payment_Method, dbo.Payment.CashPaid, dbo.Payment.ChequePaid, dbo.Payment.Remaining_Credit, dbo.Payment.Receivers_Name, 
                          dbo.Payment.ChequeNo, dbo.Payment.ChequeDate, dbo.Payment.BankName, dbo.Payment.DeliveryDate
    FROM         dbo.Items INNER JOIN
                          dbo.Receipt ON dbo.Items.ID = dbo.Receipt.ID INNER JOIN
                          dbo.Payment ON dbo.Receipt.ID = dbo.Payment.ID
    WHERE     (dbo.Receipt.ID = 1)

    I get the output twice as below.

    By twice I mean I get output of every payment for each Item & Receipt.

    But I want the output of every item + payment for each Receipt. How this might be possible.

    Hope this will let you guys understand what I mean.


    Habib Ur Rehman

    Friday, April 19, 2019 11:13 AM