CODEWITHSUNDEEP
sqloracle-databaseh2
Lico
Lico

Reputation: 75

check combination of records in table

I have two arrays of values like X,Y,Z and 1,2 There is a table A with two columns.I want to validate that in table A records with all the combination exists irrespective of duplicates. e.g.

X 1

Y 1

Z 1

X 2

Y 2

Z 2

Thanks in advance!

Upvotes: 1

Views: 2252

Answers (2)

John Doyle
John Doyle

Reputation: 7803

The following should work no matter the values:

select col1, col2
from (select distinct col1 from combtest), (select distinct col2 from combtest)
minus
select col1, col2
from combtest

First it gets the possible combinations then subtracts the actual combinations.

Upvotes: 2

Abtin Forouzandeh
Abtin Forouzandeh

Reputation: 5855

This will work with any data set and doesn't assume you know the values that will be in the table.

The query returns all the rows that are missing. You can easily turn this into an insert statement to populate your table.

SELECT *
FROM
(select * from (SELECT DISTINCT col1 FROM table1) CROSS JOIN (SELECT DISTINCT col2 FROM table1)) AS t1
LEFT OUTER JOIN table1 ON t1.col1 = table1.col1 AND t1.col2 = table1.col2
WHERE
table1.col1 is null

Upvotes: 1

Related Questions