Compare the results of two selects

[shagymoe]

I have two selects which access different tables but should return the same values since they should contain the same part numbers. I'm looking for a way to compare these values and just received a yes or no if they match. Here are the selects.

select st1p_parts.ref_code from st1p,st1p_parts where st1p.code = st1p_parts.st1_code and st1p.car_code='105072';

select st1e_parts.ref_code from st1e,st1e_parts where st1e.code = st1e_parts.st1_code and st1e.car_code='105072';

I would also like to have it automatically search all matching car_code s so that I don't have to do each one manually. This is in Oracle.

Any suggestions?

 

[carp]

I THINK this will work, although I haven't actually tested it. The DECODE takes it out of the realm of "ANSI Standard SQL", but I don't know if that's a real factor for you or not.

SELECT DECODE(COUNT(*),0,'YES','NO')
FROM
(select st1p_parts.ref_code
from st1p,st1p_parts
where st1p.code =st1p_parts.st1_code and st1p.car_code='105072'
MINUS
select st1e_parts.ref_code
from st1e,st1e_parts where st1e.code =st1e_parts.st1_code and st1e.car_code='105072' );

 

[shagymoe]

It does execute, but it returns yes when I put in two different car_codes. I think maybe this is just telling me that they have the same NUMBER of codes. I need to know if the codes are the same or different.
P car_code E car_code
5GF34HS5AB 5GF34HS5AB
5GH56DX8AB 5GH56DX8AC

In the above example, the first one is the same but in the second one they are different. The E car_code has an AC on the end, not like the AB from the P car_code.

Thanks for the help though.

 

[carp]

You're right - it doesn't work quite right.
If the unique code was in the P table, it would give you a "No".
It looks like the minus operator tells you about rows in the first table that aren't in the second.

The workaround is ugly, but works (I HAVE tested this one!):

SELECT DECODE(count(*),0,'YES','NO') all_matched
FROM (select st1p_parts.ref_code
from st1p,st1p_parts
where st1p.code =st1p_parts.st1_code and st1p.car_code='105072'
MINUS
select st1e_parts.ref_code
from st1e,st1e_parts where st1e.code =st1e_parts.st1_code and
st1e.car_code='105072'
UNION ALL
select st1e_parts.ref_code
from st1e,st1e_parts where st1e.code =st1e_parts.st1_code and
st1e.car_code='105072'
MINUS
select st1p_parts.ref_code
from st1p,st1p_parts
where st1p.code =st1p_parts.st1_code and st1p.car_code='105072');

This works, but is not only ugly, but also inefficient. But if your tables are fairly small, you might be able to use it.

Wait a while, though. Somebody else on this forum will craft a thing of beauty for you!

 

[shagymoe]

Thanks a lot! I think that did the trick.