check combination of records in table

Question

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!

Answer 1

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

Answer 2

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.