Comparrison Logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Comparrison Logic

Author

Topic

KevinMesiab
Starting Member

6 Posts

Posted - 2003-10-02 : 19:19:03

I have a table of Tests.

tblTests
TestName, TestResult

Each Test has a potential one to many relationship with "Requirements."

tblRequirements
TestID, RequirementName, RequirementValue

Now I programmatically receive a list of these Name Value pairs from an external device, and I need to find a test that matches EXACTLY the requirements I received.

For example:

I have 3 tests in the queue.

Test1 requires:

name | value
--------------
w | 5
x | 4
y | 99
z | "cde"

Test2 Requires

name | value
------------
x | 922
y | 2

Test3 Requires

name | value
------------
u | 1
v | 2
w | 3
x | 4
y | 5
z | "abc"

Device sends:

name | value
------------
x | 4
y | 99
z | "cde"

The device may send more than x, y and z, or it may send less. A test may require more than x, y and z or it may require less. How can I write a query to find a perfect match?

Thanks in advance!

Dare to innovate.

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-10-02 : 19:28:21

How do you get the data back?
Does it come into a table to be processed?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-10-02 : 19:43:40

It is hard to tell what you want here, but it looks like relational division.. Do a search on the web... Ah heck here is the link [url]http://www.dbazine.com/celko1.html[/url]

DavidM

"SQL-3 is an abomination.."

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-10-02 : 20:27:57

Yeah, this one is pretty nasty methinks. I've love to see a solution if you get one.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

KevinMesiab
Starting Member

6 Posts

Posted - 2003-10-02 : 20:29:32

It seems like relational division is just the ticket, I'll test it in a few hours and post the working sql

Dare to innovate.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-03 : 12:02:46

OK, there's a lot here, but here's one thing I've tried.

first, the DDL and the data:


create table #t1
	(ID int not null,
	Attribute varchar(10) not null,
	Value int not null,
constraint t1_pk primary key (ID, Attribute))

create table #t2
	(ID int not null,
	Attribute varchar(10) not null,
	Value int not null,
constraint t2_pk primary key (ID, Attribute))

GO

insert into #t1
select 1,'a',1 union 
select 1,'b',2 union
select 1,'c',3 union
select 2,'a',4 union
select 2,'c',5 union
select 2,'d',6 union
select 3,'a',9 union
select 3,'b',10 union
select 4,'z',2 union
select 5,'a',4 union
select 5,'b',2

insert into #t2
select 100,'a',1 union 
select 100,'b',2 union
select 100,'c',3 union
select 200,'a',4 union
select 200,'c',5 union
select 200,'d',6 union
select 300,'a',1 union
select 300,'b',2 union
select 400,'c',5 union
select 400,'d',6

The goal: return exact matches of sets of Attribute/Values between #t1 and #t2

in our sample data, 1 should match 100, and 2 should match 200.

Step1 : we will start with a cross join of all the data in t1, with each possibile matching ID from t2:


select t1.id as t1ID, t2.id as t2ID, t1.attribute, t1.value
from
 #t1 t1
cross join (select distinct id from #t2) t2

we will UNION that with all data in t2, with each possible matching ID from t1 (basically, the opposite of the first one)


union all

select t1.id as t1ID, t2.id as t2ID, t2.attribute, t2.value
from
 #t2 t2
cross join (select distinct id from #t1) t1

Look at those results. Doesn't look like much, but now we can GROUP BY t1ID, t2ID, attribute and Value and return the count:


select 
  t1ID, t2ID, attribute, value, count(*) as Matches
from
  (above SQL with the union all) a
group by 
  t1ID, t2ID, attribute, value

Look at those results. NOte that because of the GROUP BY, we know if "Matches" = 2 we have a match between t1 and t2 for that attribute/value pair.

So, we do one more layer of the above query, and ensure that we return only t1ID and t2ID where all matches = 2:


select t1ID, t2ID
from
(above SQL)
group by t1ID, t2ID
having min(Matches) =2

and voila ! that's our result. Only EXACT matches for sets of data between t1 and t2.

Full query:


select 
  t1ID, t2ID
from
(
  select 
    t1ID, t2ID, attribute, value, count(*) as Matches
  from
    (
       select 
         t1.id as t1ID, t2.id as t2ID, t1.attribute, t1.value
       from
         #t1 t1
       cross join 
         (select distinct id from #t2) t2

       union all

       select 
         t1.id, t2.id, t2.attribute, t2.value
       from
         #t2 t2
       cross join 
        (select distinct id from #t1) t1
     ) a
     group by 
       t1ID, t2ID, attribute, value  
  ) b
group by 
  t1ID, t2ID 
having 
  min(matches) = 2

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 12:04:45

Outstanding...

unlike me out standing in left field...

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

KevinMesiab
Starting Member

6 Posts

Posted - 2003-10-03 : 14:58:05

Yes, your solution is correct. I'm using something quite similar, however, you've one upped me by doing the cross join and eliminating the need for one extra nested select I've used.

Thanks guys, this was a good exercise is Linear vs Log Searching.

Dare to innovate.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 15:05:46

quote:
Originally posted by KevinMesiab
you've one upped me by doing the cross join and eliminating the need for one extra nested select I've used.

Never fear when Mr. Cross Join is near!

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

KevinMesiab
Starting Member

6 Posts

Posted - 2003-10-03 : 15:08:07

One more question to throw in

I'm comparing Name = Value pairs. In my sql, I'm using a statement,

WHERE ConfigValue IN (x,y,z)

But the match should be on both fields, for example:

WHERE ConfigName = x AND ConfigValue = 1

How can I set this up so that both things match?

Dare to innovate.

X002548
Not Just a Number

15586 Posts

Posted - 2003-10-03 : 15:19:58

quote:
Originally posted by KevinMesiab

One more question to throw in

I'm comparing Name = Value pairs. In my sql, I'm using a statement,

WHERE ConfigValue IN (x,y,z)

But the match should be on both fields, for example:

WHERE ConfigName = x AND ConfigValue = 1

How can I set this up so that both things match?

Dare to innovate.

Do you mean you want to Dynamically (hint..hint) supply the column names of a table then pair them with predicate?

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

KevinMesiab
Starting Member

6 Posts

Posted - 2003-10-03 : 15:37:57

No, I don't think so, unless I'm misunderstanding your statement.

I want to do a standard, IN clause, but I want to specify a dual IN clause, where a row should match the first IN, but in order to evaluate as true, that first in match, must match a second param,

Dare to innovate.

KevinMesiab
Starting Member

6 Posts

Posted - 2003-10-03 : 16:19:46

Problem solved. Thanks all of you for your tremendous help. I will deffinately be back in the future.

Dare to innovate.

AK
Starting Member

27 Posts

Posted - 2003-10-17 : 07:15:53

Going back to the original question - am I going mad or does it simplify to this?:

select id1=a.id, id2=b.id
from #t1 a
left join #t2 b on a.value=b.value and a.attribute=b.attribute
group by a.id, b.id
having count(*)=(select count(*) from #t1 c where a.id=c.id)
and count(*)=(select count(*) from #t2 d where b.id=d.id)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-17 : 08:25:22

YES! you can ... i took another direction in presenting my solution (i was thinking of being able to provide all differences if needed or % matches or things like that) but the most direct method would be just like the one you provided. only use an approach like mine if you think you might need to do extra analysis or find closest matches or things like that.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources