Hello experts, I want to compare two tables as per requirement.
(2) The first comparison
--------------------------------
select field1, field2 from table1 where field1='ABCD' minus select field1, field2 from table2 where field1='ABCD';
The above SQL works fine. I want to execute this for all different values of field1 in table1 and table2. How to do this ? Shall i use for loop in PLSQL ?
(2) The second comparison
---------------------------------------
select a.field1, a.field2 from table1 a, table2 b where a.field3=b.field3 and a.field4=b.field4 and a.field1='ABCD' and b.field1='ABCD';
I want to pass a.field1='ABCD' and b.field1='ABCD' from first comparison mentioned above and it should iterate for all values of field1 in table1 and field1 in table2.
Please suggest me how to do this. The fileds field1, field2, field3, field4 in both table1 and table2 are varchar2 with same structure.
Thanks
Hi,
Can you show sample data and your require output in tabular format.
Regards,
Prashant
SQL> select field1, field2, field3, field4 from table1 where field1='ABCD';
ABCD RW11L S15514887 W047553920
ABCD RW11R S15525042 W047562394
ABCD RW29L S15524330 W047543328
ABCD RW29R S15514195 W047535189
SQL> select field1, field2, field3, field4 from table2 where field1='ABCD';
---- ----- --------- ----------
ABCD RW29L S15524330 W047543328
ABCD RW29L S15524330 W047543328
ABCD RW29L S15524330 W047543328
ABCD RW29L S15524330 W047543328
ABCD RW29L S15524330 W047543328
ABCD RW29L S15524330 W047543328
ABCD RW11C S15514887 W047553920
ABCD RW11 S15525042 W047562394
ABCD RW29C S15524330 W047543328
ABCD RW29 S15514195 W047535189
ABCD RW29L S15524330 W047543328
ABCD RW29L S15524330 W047543328
ABCD RW29L S15524330 W047543328
ABCD RW11C S15514887 W047553920
ABCD RW11 S15525042 W047562394
ABCD RW29C S15524330 W047543328
ABCD RW29 S15514195 W047535189
ABCD RW29L S15524330 W047543328
regards
Hi,
Below mentioned queries can help you out in finding the solution, below queries are cross checked with your first solution of getting result set on minus operations.
Field 2
SELECT * FROM TAB1 a where not exists (select 1 from TAB2 b where a.field1=b.field1 and a.field2=b.field2);
Field 3
SELECT * FROM TAB1 a where not exists (select 1 from TAB2 b where a.field1=b.field1 and a.field3=b.field3);
Field 4
SELECT * FROM TAB1 a where not exists (select 1 from TAB2 b where a.field1=b.field1 and a.field4=b.field4);
Regards,
Prashant Dabral
HI Prashant Thanks
I have created two new tables with field1 values only 'ABCD' and tested your SQL, it works fine.
If i run the same SQL for entire table with different values in field1 (for example, KABY, PQBJ, KIOP etc), it gives incorrect result.
I have changed your query from what i understood and i didn't tested. If you provide create table and insert script,required output then i will test this and give to you. So can you please try the below query and let me know in case of any issues.
First Query:-
------------
select field1, field2 from table1 where field1='ABCD' minus select field1, field2 from table2 where field1='ABCD';
Modified Query:-
----------------
SELECT t1.field1,
t1.field2
FROM table1 t1,table2 t2
WHERE t1.field1 = t2.field1
AND t1.field2 <> t2.field2;
(OR)
SELECT field1,
field2
FROM table1 t1
WHERE EXISTS(SELECT 1
FROM table2 t2
WHERE t1.field1 = t2.field1)
AND NOT EXISTS(SELECT 1
FROM table2 t2
WHERE t1.field2 = t2.field2);
Second Query:-
--------------
select a.field1, a.field2 from table1 a, table2 b where a.field3=b.field3 and a.field4=b.field4 and a.field1='ABCD' and b.field1='ABCD';
Modified Query:-
---------------
SELECT a.field1,
a.field2
FROM table1 a,table2 b
WHERE a.field1 = b.field1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND a.field2 <> b.field2;
(OR)
SELECT field1,
field2
FROM table1 a,table2 b
WHERE a.field1 = b.field1
AND a.field3 = b.field3
AND a.field4 = b.field4
AND NOT EXISTS(SELECT 1
FROM table2 t2
WHERE a.field2 = t2.field2)
AND NOT EXISTS(SELECT 1
FROM table1 t1
WHERE b.field2 = t1.field2);
Hi, Thanks
The data is given third thread.
(1) First I am trying to select field1 and field2 that are present table1 but not present in table2, so the output is given below.
ABCD RW11L
ABCD RW11R
ABCD RW29R
(2) For a given output from step1, I am trying to select field1 and field2 if their field 3 and field 4 matches with field3 and field 4 values in table2. The output is given below.
ABCD RW11L
ABCD RW11R
ABCD RW29R
Those three rows are present in TABLE1 and not present in TABLE2 and these three rows as well match their FIELD3, FIELD4 values in TABLE1 and TABLE2
Hope I explained the problem.
Hi,
Hope i could get somewhere to your exact requirement.
WITH T AS (SELECT * FROM TAB1 A WHERE NOT EXISTS (SELECT 1 FROM TAB2 B WHERE A.FIELD1=B.FIELD1 AND A.FIELD2=B.FIELD2)) SELECT * FROM T WHERE EXISTS (SELECT 1 FROM TAB2 B WHERE T.FIELD1=B.FIELD1 AND T.FIELD3=B.FIELD3 AND T.FIELD4=B.FIELD4)
Output
FIELD1 | FIELD2 | FIELD3 | FIELD4 |
---|---|---|---|
ABCD | RW11L | S15514887 | W047553920 |
ABCD | RW11R | S15525042 | W047562394 |
ABCD | RW29R | S15514195 | W047535189 |
First of all, there is a FAQ that explains how to ask questions on this forum: Re: 2. How do I ask a question on the forums?
One thing to do is provide CREATE TABLE and INSERT statements so we can test our solutions. For example:
create table table1(field1, field2, field3, field4) as select
'ABCD', 'RW11L', 'S15514887', 'W047553920' from dual union all select
'ABCD', 'RW11R', 'S15525042', 'W047562394' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29R', 'S15514195', 'W047535189' from dual;
create table table2(field1, field2, field3, field4) as select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW11C', 'S15514887', 'W047553920' from dual union all select
'ABCD', 'RW11', 'S15525042', 'W047562394' from dual union all select
'ABCD', 'RW29C', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29', 'S15514195', 'W047535189' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW11C', 'S15514887', 'W047553920' from dual union all select
'ABCD', 'RW11', 'S15525042', 'W047562394' from dual union all select
'ABCD', 'RW29C', 'S15524330', 'W047543328' from dual union all select
'ABCD', 'RW29', 'S15514195', 'W047535189' from dual union all select
'ABCD', 'RW29L', 'S15524330', 'W047543328' from dual;
Next, try to express the requirement without procedural language. When you say "first", "then", "loop" and such you are thinking in procedural code and not in sets. For SQL try to think in sets. If I understand your requirement, here is a solution:
select * from table1
where (field1, field2) not in (
select field1, field2 from table2
)
and (field1, field3, field4) in (
select field1, field3, field4 from table2
);
FIELD1 | FIELD2 | FIELD3 | FIELD4 |
---|---|---|---|
ABCD | RW11L | S15514887 | W047553920 |
ABCD | RW11R | S15525042 | W047562394 |
ABCD | RW29R | S15514195 | W047535189 |
Warning: this solution will only work if field1 and field2 are NOT NULL! If they can be null, we need to replace NOT IN by WHERE NOT EXISTS.
Hi Prashant, Thanks, It works fine.
Can you please explain SELECT 1 ?
Does 1 in SELECT statement represents first field in a table or it has different meaning ?
Then try this.. It will give correct result as you expected
SELECT field1,
field2
FROM table1 t1
WHERE NOT EXISTS(SELECT 1
FROM table2 t2
WHERE t1.field2 = t2.field2)
AND EXISTS (SELECT 1
FROM table2 t2
WHERE t1.field3 = t2.field3
AND t1.field4 = t2.field4);
OUTPUT:-
------
FIEL FIELD
---- -----
ABCD RW11R
ABCD RW29R
ABCD RW11L
Hi Stew, Thanks for your suggestions and SQL query. It works fine and I have tested the result.
I have one question in WHERE clause.
I understood first condition, But in second condition, there is no field2, Kindly explain.
where (field1, field2) not in (
select field1, field2 from table2
)
and (field1, field3, field4) in (
select field1, field3, field4 from table2
);
Hi,
When using the EXISTS keyword you need to have a sub-select statement, and only
the existence of a row is checked, the contents of the row do not matter.
SELECT is a keyword that controls what is inside the columns that are returned.
SELECTing 1 or any other column will return the same number of rows.
Also, If your problem is solved then mark thread as answered so that people can concentrate on others unresolved threads.
Regards,
Prashant Dabral
don123 wrote:
in second condition, there is no field2, Kindly explain.
where (field1, field2) not in (
select field1, field2 from table2
)
and (field1, field3, field4) in (
select field1, field3, field4 from table2
);
There is no field2 in your second condition either. Why do you think there should be?
Remember, there are two conditions connected by AND: both conditions have to be true.
Come to think of it, there is no reason to have field1 in the first condition! Logically, your requirement could simply be: rows in table1 that are also in table2, except field2 has to be different.
select * from table1
where field2 not in (
select field2 from table2
)
and (field1, field3, field4) in (
select field1, field3, field4 from table2
);