I want to check that the poi_equipement table (relationship table) corresponds to the data in the data table (i.e. a two-way check)

https://dbfiddle.uk/gFMjbIpX

detect that wc (in poi_equipement) is extra (because it is not present in the data table) and that hotel is not in poi_equipement so it is absent compared to the data table

I don't understand why with the raquête except he just answers me hotel. I want him to answer me hotel and wc.

select object from data where subject = 'url1'
except
select subject from poi_equipement inner join equipement on poi_equipement.equipement_id = equipement.id;

ideally I want to know when I have a difference in poi_equipement, in data or in the 2 tables

CodePudding user response：

A full outer join will do

with params as (
select 'url1' as subject),
data_object as (
select d.object
  from data d
  join params prm
    on d.subject = prm.subject),
equipment_subject as (    
select e.subject
  from poi_equipement pe 
  join poi p
    on pe.poi_id = p.id
  join equipement e 
    on pe.equipement_id = e.id 
  join params prm
    on p.id_url = prm.subject)
select d.object    as data,
       e.subject   as poi_equipment
  from data_object d
  full outer
  join equipment_subject e
    on d.object = e.subject
 where d.object is null
    or e.subject is null;

Result:

data |poi_equipment|
----- ------------- 
hotel|             |
     |wc           |

You can remove where clause if you need to see which item is in both places.