If I do
SELECT COUNT(*) as NUMBERRESULTS
from amsuser.car
I return 82 results, but if
SELECT COUNT(*) as NUMBERRESULTS
from amsuser.car
left join amsuser.custhist RW on loan.CUST_NUMBER = RW.cust_number AND loan.ACCOUNT_NUMBER = RW.ACCOUNT_NUMBER AND RW.trans_code='AJ' AND loan.LOAN_CODE='RW'
left join amsuser.custhist RO on loan.CUST_NUMBER = RO.cust_number AND loan.ACCOUNT_NUMBER = RO.ACCOUNT_NUMBER AND RO.trans_code='RG' AND loan.LOAN_CODE='RW'
I get 812 results? How can the joins be adding rows?
Anupam SinghPosted May 21, 2014, 9:00 AM
yes.. here is the solution then. actually the problem is duplicate records right?
so first we need to take distinct records then we can apply join .
try this (please refactor this b'coz i wrote this in notepad not in ssms)
WITH right_table as
(
SELECT selery,PF,ROW_NUMBER() OVER(PARTITION by ID ORDER BY id)
AS duplicaterec
FROM table1
)
select ltbl.col1,rtbl.col2, rtbl.col1,rtbl.col2 from // here we can apply join
right_table rtbl join left_table ltbl on ltbl.id=rtbl.id
WHERE rtbl.duplicaterec = 1 //should be 1
patrickPosted May 21, 2014, 9:30 AM
Anupam SinghPosted May 21, 2014, 9:28 AM
With :With is basically use to set data in temporary variable.(this is my definition.)
Partition : PARTITION BY Divides the result set into partitions .
PARTITION BY Divides the result set into partitions. partition by just works on a window function, like row_number.(this is what google says :) b'coz is used it many time but unknowingly)
patrickPosted May 21, 2014, 9:17 AM
patrickPosted May 21, 2014, 8:43 AM
Anupam SinghPosted May 21, 2014, 8:34 AM
You can use subquery if join doesnt fulfill your requirement .
like :
select t1.id,t1.name,t1.age,(select top(1) s.sslery from salery s where s.id=t1.id ) as selery from table1 t1
you can use more subquery as per your need ..
hope this will help.
patrickPosted May 21, 2014, 8:27 AM
Jignesh TrivediPosted May 21, 2014, 8:24 AM
you have to take care if you want to single records....
patrickPosted May 21, 2014, 8:22 AM
I think I understand, although I was under the assumption that left join returned only one record for each record in table A, regardless of the records in table B;
So I need to make sure that my parameters in the join only return 1 record for my custhist table?
Jignesh TrivediPosted May 21, 2014, 12:29 AM
The SQL LEFT Join returns all rows from the left table even if there are no matching in right table.
This join always match record on the column which are passed after on key word.
In you case, It returns multiple result because it may be custhist table contain multiple record in combination of cust_number, account_number ,trans-code and loan_code.
hope this will help you.