December 4, 2009 at 9:41 am
I need to compare results from 2 tables. Easy, I just have to use the Union All. But this shows me the difference in records between the 2 tables. What I really need to do is compare a set of the values in TableA vs. the set of values in TableB.
For example:
This is the result of my TableA. (note: this table only has this values, don’t have any id)
SegmentValue
1
2
3
This is the result of my TableB
ID SegmentValue
11
12
13
2A
2B
The set of values of TableA (1,2,3) match with the set of values of TableB (1,2,3). If only one set of my TableB match the entire values of the TableA, then I proceed with something else
I have this, but this only shows the difference of records that don’t match.
Select SegmentValue
from (
Select distinct SegmentValue
From #profileSegment -- TableA
Union ALL
Select distinct SegmentValue = SegmentationTagName
FromSegmentationTag st -- TableB
JoinSegmentation s on s.SegmentationId = st.SegmentationId
Wheres.ClientId = 28 ) T
Group by SegmentValue
Having count(*) = 1
order by SegmentValue
I tried to use the EXCEPT operator, but I don’t know how to compare for set of data (group by Id). This example matches what I need, but only for the results that I showed for TableB.
Select distinct SegmentValue from #profileSegment
EXCEPT
selectdistinct SegmentValue = SegmentationTagName
FromSegmentationTag st
JoinSegmentation s on s.SegmentationId = st.SegmentationId
Wheres.ClientId = 28
If TableB has these values, the except operator “match” the set of values of TableA with the first 3 rows of the TableB. But, this is a entire set of values that WONT match the entire set of values that are in TableA
ID SegmentValue
11
12
13
14
15
How can I compare a set of values from one table to another?
December 4, 2009 at 11:02 am
Will this do what you need?
select ID
from TableB
inner join TableA
on TableB.SegmentValue = TableA.SegmentValue
group by ID
having count(*) =
(select count(*)
from TableA);
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 4, 2009 at 11:26 am
did you try it with the 2 select (TableA and TableB) that I post?
Because It didnt work for me when I tried your advice
Thank you
December 4, 2009 at 2:41 pm
one of my Colleagues find a faster way. He compared the total of set and then try to match the values with TableB
Hope can help to someone...
declare @UniqueSegmentationIDs int
declare @UnmatchSegmentationIDs int
select @UniqueSegmentationIDs = count(segmentationID) from
(
select distinct st.SegmentationID
From SegmentationTag st -- TableB
Join Segmentation s on s.SegmentationId = st.SegmentationId
Where s.ClientId 28
group by st.SegmentationId
) t
select @UnmatchSegmentationIDs = count(segmentationID) from
(
select distinct st.SegmentationID
From SegmentationTag st -- TableB
Join Segmentation s on s.SegmentationId = st.SegmentationId
left join #profileSegment p on p.SegmentValue = st.SegmentationTagName
--and s.SegmentationID = 86
Where s.ClientId 28 and p.SegmentValue is null
) t1
select @UnmatchSegmentationIDs, @UniqueSegmentationIDs
-- if(@UnmatchSegmentationIDs < @UniqueSegmentationIDs)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply