July 14, 2011 at 11:31 am
I am trying to get a record from the left table that should match a record on the right side which should not have a corresponding record with different type.
Is there a way to do this efficiently without joining twice as these tables are huge.
CREATE TABLE [dbo].[Credit](
[aID] [int] IDENTITY(1,1) NOT NULL,
[aClaimno] [varchar](50) NOT NULL,
[adt] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[RCclaim](
[rcID] [int] IDENTITY(1,1) NOT NULL,
[rcClaimno] [varchar](50) NOT NULL,
[rcType] [int] NOT NULL
) ON [PRIMARY]
INSERT INTO [Credit] ([aClaimno],[adt]) VALUES ('c1','01/01/2011')
INSERT INTO [Credit] ([aClaimno],[adt]) VALUES ('c2','01/01/2011')
INSERT INTO [Credit] ([aClaimno],[adt]) VALUES ('c3','01/01/2011')
INSERT INTO [Credit] ([aClaimno],[adt]) VALUES ('c4','01/01/2011')
INSERT INTO [RCclaim] ([rcClaimno],[rcType]) VALUES ('c1',0)
INSERT INTO [RCclaim] ([rcClaimno],[rcType]) VALUES ('c1',1)
INSERT INTO [RCclaim] ([rcClaimno],[rcType]) VALUES ('c2',0)
INSERT INTO [RCclaim] ([rcClaimno],[rcType]) VALUES ('c3',0)
INSERT INTO [RCclaim] ([rcClaimno],[rcType]) VALUES ('c3',1)
My query is like this
select c.* from Credit c
inner join rcclaim a on aclaimno = a.rcclaimno and a.rctype = 0
left join rcclaim b on aclaimno = b.rcclaimno
and b.rctype = 1 where b.rcid is null
Expected Output:
3c22011-01-01 00:00:00.000
I appreciate your inputs..
July 14, 2011 at 11:46 am
Would this work?
SELECT aClaimNo, adt
FROM Credit
JOIN RCclaim ON aclaimno = rcclaimno
GROUP BY aClaimNo, adt
HAVING COUNT(*) = 1
July 14, 2011 at 11:50 am
I have got your expected results but not sure if this is what you want.
select a.rcID,a.rcClaimno,c.adt from Credit c
join rcclaim a on aclaimno = a.rcclaimno
and a.rctype = 0
left join rcclaim b on aclaimno = b.rcclaimno
and b.rctype = 1
where b.rcid is null
July 14, 2011 at 11:51 am
bopeavy that is exactly the query he wrote 😛 he wants to avoid having two joins on the table.
July 14, 2011 at 11:53 am
No it is not the exact query he wrote because it gives the output hes looking for his query did not give the expected results. I understand he did not want to joins.
July 14, 2011 at 12:05 pm
bopeavy, kramaswamy is right.
My query gives me the right result and you have the same. I am trying to avoid 2 joins that is all.
Thanks for the attempt. Looks like we are on the same page.
kramaswamy, yours seems to work for me currently as I am not expecting more than one entry for type=0 but will not scale if that situation changes.
Thanks guys.
July 14, 2011 at 12:12 pm
Are you saying if there is a claim number which has two rcclaim records, but which has both rctypes the same, the record should be returned? IF so, this should work.
SELECT aClaimNo, adt
FROM Credit
JOIN RCclaim ON aclaimno = rcclaimno
GROUP BY aClaimNo, adt
HAVING MAX(rcType) = MIN(rcType)
July 14, 2011 at 12:15 pm
kramaswamy in the original post he had both rctype =1 and rctype = 0 in the joins.
July 14, 2011 at 12:15 pm
What will happen if there are 2 differnct RcTypes other than 0 ?
July 14, 2011 at 12:17 pm
See my edited post - the changed version of the query should work for returning only records which have a single rctype.
July 14, 2011 at 12:19 pm
rctype value is only a sample I gave. it can be 2 or 3 anything but i will know it before hand.
max(a.rcType)= MIN(a.rcType) seems to work
thanks kramaswamy
July 14, 2011 at 12:21 pm
ColdCoffee
What will happen if there are 2 differnct RcTypes other than 0 ?
Not sure probably something close to Maximum Overdrive.:w00t:
July 14, 2011 at 4:33 pm
I confess to being curious whether or not all the grouping will be faster than the joins we're trying to avoid.
Just for fun, here's another way of looking at it. With the right indexing, it could be quick.
-- declare and populate tables
declare @rcclaim table (rcclaimno int, rctype int, rcddata varchar(50), primary key (rcclaimno,rctype))
declare @credit table (CreditID int primary key,CreditRef char(1),CreditAmt int,AclaimNo int)
insert into @rcclaim
select 1,0,'Claim 1 Type 0' union all
--select 1,1,'Claim 1 Type 1' union all
--select 2,0,'Claim 2 Type 0' union all
select 2,1,'Claim 2 Type 1' union all
select 3,0,'Claim 3 Type 0' union all
select 3,1,'Claim 3 Type 1' union all
select 4,2,'Bogus in all variations'
insert into @credit
select 1, 'A', 100, 1 union all
select 2, 'B', 150, 2 union all
select 3, 'C', 250, 3
-- select rows
-- in production, it would be sweet to have an index on rcclaimno,rctype, even if it wasn't unique
;with cte as (select rcclaimno
,SUM(case when rctype = 0 then 1 else 0 end) as typeZeroes
,COUNT(*) as alltypes
from @rcclaim r
group by rcclaimno
)
select c.*
from cte
join @credit c on c.AclaimNo = cte.rcclaimno
where typeZeroes > = 1 and alltypes = typeZeroes
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply