CODEWITHSUNDEEP
sqlselectoracle10g
Vaandu
Vaandu

Reputation: 4945

Select Query to generate and exclude some results by joining two tables

I have two tables named, config and config_exceptions. Both has same structure. Columns are home, configName, data, active.

config table has below data

0 config1 my_data1 active
0 config1 my_data2 active
0 config1 my_data3 active
0 config2 my_data3 active
2 config1 my_data1 active
2 config2 my_data1 active

config_exceptions table has below data

10 config1 my_data1 active

I need to write a query to exempt config_exceptions data with home 0 alone to give results, like below

0 config1 my_data1 active
0 config1 my_data2 active
0 config1 my_data3 active
0 config2 my_data3 active
2 config1 my_data1 active
2 config2 my_data1 active
10 config1 my_data2 active
10 config1 my_data3 active

This result is creating a new set of config results for home 10 using home 0 and exclude the config_exceptions table data. main columns for comparing is home and configName.

And I can't hard code as 10, they will be more, but 0 can be hard coded as it is used as base.

Please help me to write this query, I don't have idea. Please give me some idea.

Upvotes: 0

Views: 164

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

Well, with your sample, this does work.

SELECT home, configName, data, active
FROM config

UNION

SELECT ce.home, c.configName, c.data, c.active
FROM config_exceptions ce
INNER JOIN config c ON c.configName = ce.configName
WHERE ce.data <> c.data
AND c.home = 0

SqlFiddle

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270523

I think something like this:

select *
from ((select home, configName, data, active
       from config
      ) union all
      (select 10 as home, configName, data, active
       from config c left outer join
            config_exceptions ce
            on c.home = ce.home and
               c.configName= ce.configName and
               c.active = ce.active
       where config = 0 and
             ce.home is null
      )
     )
order by 1, 2, 3, 4

Upvotes: 0

Related Questions