|
You have two matching rows in the transaction table for each customer, and you've asked SQL to return all of them, so it's hardly surprising that you get two rows back per customer. If you only want the most recent transaction for each customer, there are ways to do that. However, it would help if you told us which DBMS you're using.
Also, try to avoid using old-style joins:
... FROM customer c,transaction t WHERE c.customer_id = t.customer_id And ...
Use an INNER JOIN instead:
... FROM customer c INNER JOIN transaction t ON c.customer_id = t.customer_id WHERE ...
It makes it much easier to separate the joining conditions from the filtering conditions, and to switch between different types of join.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Mysql database we are using.yes my transaction table is having two records per customer id, but paid amounts and other fields are different ,
but here the query is giving last record values for two records having same customerid.
Transaction table doesn't contain primary key,so there exists more than one record per customer id.
and i want most recent record from Database.
modified 3-Apr-14 3:41am.
|
|
|
|
|
I'm not overly familiar with MySql, but something like this should do the trick:
SELECT
...
FROM
customer c
INNER JOIN transaction t
ON t.customer_id = c.customer_id
INNER JOIN
(
SELECT customer_id, Max(paid_date) As LastPaid
FROM transaction
WHERE DATE(paid_date) BETWEEN '2014-02-02' AND '2014-04-02'
GROUP BY customer_id
) t2
ON t2.customer_id = t.customer_id
And t2.LastPaid = t.paid_date
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I guess it's safe to assume that it's monthly payments, and that BETWEEN '2014-02-02'AND '2014-04-02' you'd have two payments per customer_id.
|
|
|
|
|
Where do you display the result? In a .Net application written by you / your colleagues? In MySQL Query browser? ...?
|
|
|
|
|
am generating EXcel/ PDF file,in .net application written by me,
modified 3-Apr-14 5:19am.
|
|
|
|
|
Consequently, the first thing to check is if the data returned from the query to your application are correct, and then where that duplication happens. That's a lot of debugging...
|
|
|
|
|
|
Hi All,
I want to generate a script for an existing database in oracle 10g,
Please let me know how to generate this script using oracle 10g sql navigator interface or using script.
Thank you
|
|
|
|
|
|
I work for an Insurance Company and creating a custom application. When we apply a coverage to a policy it is stored in a table with a Limit, Deductible, and Premium field. However, there are many coverages that do not follow the norm. They use different fields for Limit, Deductible, and Premium and are even stored in different tables. (Don’t ask why and don’t even try to understand the fact that they are STILL doing this so I need to leave logic to allow for additional coverages like this.) There is also no rhyme or reason to what fields they choose so varies greatly.
So, we have decided to create “LookUp” tables that will store all the information we need for a coverage. This avoids numerous joins – we were having issues with speed and performance of the application.
I need to find a way to prepopulate all these fields into a table. I would like the best option performance-wise. This will end up being a Nightly job – so when we receive a new snapshot of data we will run this query and prepopulate/update the values of the table.
Here is an example of my table, it contains the Coverage name (Coverage), the table (File), and the fields for the Limit, Deductible, and Premium. The PolicyNum field is a concatenation of LOB + Policy + Module. I need all three of these fields to join to a policy. I wanted to use a table since they will be adding new coverages. Keep in mind, I created this tables so can add more fields to it.
Here is the table I am referring to:
USE [PolicySummary]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Coverage](
[CoverageID] [int] IDENTITY(1,1) NOT NULL,
[LOB] [nvarchar](255) NULL,
[Coverage] [nvarchar](255) NULL,
[Description] [nvarchar](255) NULL,
[File] [nvarchar](255) NULL,
[Limit] [nvarchar](255) NULL,
[Deductible] [nvarchar](255) NULL,
[Premium] [nvarchar](255) NULL,
[IsMultLimit] [bit] NULL,
[IsMultDeductible] [bit] NULL,
[IsMultPremium] [bit] NULL,
[InsuranceLine] [nvarchar](255) NULL,
[PolicyNum] [nvarchar](255) NULL,
CONSTRAINT [PK_Coverage] PRIMARY KEY CLUSTERED
(
[CoverageID] 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
So in a nutshell, a policy may have the coverage BP0713. I then to look at this table and see that I need to run the following query:
SELECT BYAGVA as limit, BYPPTX as Deductible, BYA3VA as Premium FROM ASBYCPP WHERE BYARTX+BYASTX+BYADNB = @PolNum
The table contains the table name, the 3 fields, and the concatenation for the Policy Number.
Currently, I am looping through each coverage in a policy and selecting the values one at a time in a WHLIE Loop. Works but it is slow and I am hitting the query numerous times just to open the app (numerous units per policy so will be ran for each unit).
SELECT @SQL = N'SELECT @Limit=' + ISNULL(Limit, '''''') + N',@Deductible=' + ISNULL(Deductible, '''''') + N',@Premium=' + ISNULL(Premium, '''''') + N' FROM PHI_PIJ.dbo.' + [File] + N' WHERE ' + PolicyNum + N' = ''' + @PolNum +'''' + N' AND ' + @CovField + N'='''+Coverage +''''
FROM PolicySummary.dbo.Coverage
WHERE Coverage = @Coverage
AND LOB = @LOB
EXEC sp_executesql @SQL, N'@Limit nVarChar(255) output,@Deductible nVarChar(255) output,@Premium nVarChar(255) output', @Limit output, @Deductible output, @Premium output
INSERT INTO #OptCov2 VALUES(@Coverage, @Description, @LOB, @Limit, @Deductible, @Premium)
Can I get help with finding a way to populate my “Lookup” table each night so all I would have to do is “SELECT Limit, Deductible, PREMIUM FROM CoverageLookUp WHERE PolicyNum = @PolicyNum”
Here’s what I’ve got so far. It takes nearly 4 minutes to run the top 1000 records and there are a total of 614,497 records to run.
SELECT @SQL = N'SELECT @Limit=' + ISNULL(Limit, '''''') + N',@Deductible=' + ISNULL(Deductible, '''''') + N',@Premium=' + ISNULL(Premium, '''''') + N' FROM PHI_PIJ.dbo.ASBYCPP' + N' WHERE ' + PolicyNum + N' = ''' + @PolicyKey +'''' + N' AND BYAOTX' + N'='''+Coverage +''''
FROM PolicySummary.dbo.Coverage
WHERE Coverage = @Coverage
AND LOB = 'BOP'
EXEC sp_executesql @SQL, N'@Limit nVarChar(255) output,@Deductible nVarChar(255) output,@Premium nVarChar(255) output', @Limit output, @Deductible output, @Premium output
UPDATE PolicySummary.dbo.CoverageLkup
SET Limit = @Limit, Deductible = @Deductible, Premium = @Premium
WHERE CovAbbrev = @Coverage
AND PolicyKey = @PolicyKey
|
|
|
|
|
Where is the table? 
It sounds reasonable so far.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I have edited my question to include the script I use to create the table. Sorry about that.
|
|
|
|
|
Ah, good, how about some sample data? Made up values would be fine of course.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
I had issues pasting my entire query so I just pasted the piece inside the loop. It works but seriously takes ways too long. I need to find a way from looping through them all and probably not using dynamic SQL but can't figure out a way yet.
|
|
|
|
|
I can probably help a bit once I can get other things off my mind.
Yes, removing the looping is likely to help quite a bit.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Dear if you want to learn sql by online free tutorial. this tutorial may be helpful to you.
www.javatpoint.com/sql-tutorial
post your queries on javatpoint forum.
www.javatpoint.com/forum
|
|
|
|
|
Probably wouldn't help me, but maybe the OP.
You'll never get very far if all you do is follow instructions.
|
|
|
|
|
Don't forget to create some indexes too, in order to speed things up.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
hey , i have a query that collects data from 3 tables using one inner join and left join but the problem that there are many duplicate rows
<
SELECT tbl_salles_bill.sb_id, ( tbl_salles_bill.sb_price_after_alll_dicount - tbl_salles_bill.sb_paid_money) as Dept,
tbl_customer.cust_fName + ' ' + tbl_customer.cust_mName + ' ' + tbl_customer.cust_lName AS CustomerName, tbl_customer.cust_city, tbl_receiving_money_receipt.rmr_id,
tbl_receiving_money_receipt.rmr_value AS Creditor
FROM tbl_salles_bill INNER JOIN
tbl_customer ON tbl_salles_bill.cust_id = tbl_customer.cust_id and tbl_salles_bill.sb_status='Paid'
left JOIN
tbl_receiving_money_receipt ON tbl_customer.cust_id = tbl_receiving_money_receipt.cust_id and tbl_receiving_money_receipt.rmr_IsPaidForWhat = 'Paid without recieving goods'
WHERE (tbl_customer.cust_id = 6)
thanks for your time.
>
|
|
|
|
|
Underscore make that unreadable bleh...
What I do is a 2 query pass, the inside query uses ROW_NUMBER() and PARTITION OVER the key fields (those that make up the unique record)
I then delete records with a row number > 1
Here is a snippet I keep around for deduping
DECLARE @Tbl TABLE (IDField INT, RowNo INT)
INSERT @Tbl
SELECT lnkStrategyNodeID IDfield,
ROW_NUMBER() OVER(PARTITION BY StrategyID,NodeID ORDER BY lnkStrategyNodeID) Rw
FROM lnkStrategyNode
DELETE
FROM lnkStrategyNode
WHERE lnkStrategyNodeID IN (SELECT IDField FROM @Tbl WHERE RowNo > 1)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Use the DISTINCT keyword
SELECT DISTINCT tbl_salles_bill.sb_id, ( tbl_salles_bill.sb_price_after_alll_dicount - tbl_salles_bill.sb_paid_money) as Dept,
tbl_customer.cust_fName + ' ' + tbl_customer.cust_mName + ' ' + tbl_customer.cust_lName AS CustomerName, tbl_customer.cust_city, tbl_receiving_money_receipt.rmr_id,
tbl_receiving_money_receipt.rmr_value AS Creditor
FROM tbl_salles_bill INNER JOIN
tbl_customer ON tbl_salles_bill.cust_id = tbl_customer.cust_id and tbl_salles_bill.sb_status='Paid'
left JOIN
tbl_receiving_money_receipt ON tbl_customer.cust_id = tbl_receiving_money_receipt.cust_id and tbl_receiving_money_receipt.rmr_IsPaidForWhat = 'Paid without recieving goods'
WHERE (tbl_customer.cust_id = 6)
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
|
The following error occurred while trying to connect database
ora-00257 archiver error solution in sql developer
need solution for sql developer
|
|
|
|
|
As I remember this error is caused by out-of-space in your storage, so log data can not be stored (archived) while trying to run some transaction...
Please check your disks...
I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
