SQL data, selecting and searching in the same table

Question

id1	id2	v1	v2	v3
P1	I1	A	B	C
P1	I2	D	E	F
P2	I1	A	B	D
P3	I1	A	D	E
P2	I2	B	D	E

How can I get from the table above to following:

First, I need all entries with an A, we call this table X (I think I can do this with:

SELECT *
  FROM test_table
 WHERE v1 = 'A'
    OR v2 = 'A'
    OR v3 = 'A'

id1	id2	v1	v2	v3
P1	I1	A	B	C
P2	I1	A	B	D
P3	I1	A	D	E

Second, the occurrence of each value in column v1 to v3 that is not A:

var	occur
B	2
C	1
D	2
E	1

Third, get for each id1 from table X, all entries from the original table that are not in Table X:

id1	id2	v1	v2	v3
P1	I2	D	E	F
P2	I2	B	D	E

Fourth, same statistic of occurrence an this table:

var	occur
B	1
F	1
D	2
E	2

Sorry for the long post. Probably there is a simple solution, but I am new to SQL.

Are you asking for 3 queries? It isn't clear which question you want answered here. — Tim Biegeleisen, Apr 25 '22 at 07:25
This is what I also do not know. Can I do this in one query? Does it have to be three or four? I think, for a starter, it make sense to do each step in a separate query, right? — TRK, Apr 25 '22 at 07:26
You can't do all these things in one query...the result sets are all different. — Tim Biegeleisen, Apr 25 '22 at 07:27
Please read https://meta.stackoverflow.com/questions/388759/why-should-i-tag-my-rdbms — Reporter, Apr 25 '22 at 09:20

score 1 · Answer 1 · answered Apr 25 '22 at 13:10

Hope this can be to some help it is done in oracle so you might need to edit some...

with data(id1,  id2,    v1, v2, v3) as(
select 'P1',    'I1',   'A',    'B',    'C' from dual union all
select 'P1',    'I2',   'D',    'E',    'F' from dual union all
select 'P2',    'I1',   'A',    'B',    'D' from dual union all
select 'P3',    'I1',   'A',    'D',    'E' from dual union all
select 'P2',    'I2',   'B',    'D',    'E' from dual
),
alla as (
select * from data where v1='A' or v2='A' or v3 ='A'
),
second AS
(select 'B' var ,((select count(V1) from alla where v1='B')+ (select count(V2) from alla where v2='B')+ (select count(V3) from alla where v3='B')) occur from dual union all
select 'C' var ,((select count(V1) from alla where v1='C')+ (select count(V2) from alla where v2='C')+ (select count(V3) from alla where v3='C')) occur from dual union all
select 'D' var ,((select count(V1) from alla where v1='D')+ (select count(V2) from alla where v2='D')+ (select count(V3) from alla where v3='D')) occur from dual union all
select 'E' var ,((select count(V1) from alla where v1='E')+ (select count(V2) from alla where v2='E')+ (select count(V3) from alla where v3='E')) occur from dual 
),
third as ( select distinct data.* from data
join alla on data.id1 != alla.id1 and data.id2 != alla.id2
order by data.id1)
--fourth
select 'B' var ,((select count(V1) from third where v1='B')+ (select count(V2) from third where v2='B')+ (select count(V3) from third where v3='B')) occur from dual union all
select 'C' var ,((select count(V1) from third where v1='C')+ (select count(V2) from third where v2='C')+ (select count(V3) from third where v3='C')) occur from dual union all
select 'D' var ,((select count(V1) from third where v1='D')+ (select count(V2) from third where v2='D')+ (select count(V3) from third where v3='D')) occur from dual union all
select 'E' var ,((select count(V1) from third where v1='E')+ (select count(V2) from third where v2='E')+ (select count(V3) from third where v3='E')) occur from dual

SQL data, selecting and searching in the same table

1 Answers1