Can i display the extra or missing rows in Table B when only a left or inside SQL join is currently available in QGIS

Question

I have Table A and Table B which are two shapefiles I have imported into a Geopackage and then joined using the DB Manager in QGIS to provide an output of showing where the name, cable length or geometry have change with the following code

Select a.name, A.cable_leng, a.geom, a.fid
     , case when a.name <> B.name and
                 a.cable_leng <> B.cable_leng
            then 'Cable Name and Length modified'
            when a.name <> B.name then 'Name'
            when a.cable_leng <> B.cable_leng then 'Cable Length Modified'
            when b.name is null then 'Deleted Cable'
       end Cables
      , case when a.geom <> B.geom then 'Geometry Modified'
            when a.geom = B.geom then 'No Geometry Changes'
       end Geometry
  from table_1 a
  inner join table_2 b
    on a.fid = b.fid

I get the following output in the linked image I am happy with SQL output

Example data example of data

But I would like to know what edits I can make to my query that would show when the number of rows between the two tables may be different, either extra or missing. from the research I have done I might need to do a right join, but this is not currently available with QGIS DB Manager, but then i considered the case where there might be fewer rows in Table B. SO I would possibly want to display where there are NULL values in either Table just from the left join or should i use another query type?

As a side note is there a a way to show data from Table A and Table B in the output. As I understand it, there is no way to order how the columns are displayed in the output, but it would be nice to at least be able to see the values that are different in one table, even if they cannot be ordered.

Edit: looking at my query again this line probably isn't doing anything with a left join ?

when b.name is null then 'Deleted Cable'

ScaisEdge ScaisEdge · Accepted Answer · 2019-10-24T06:05:29

Using left join you coul select also from table_2 the not matching rows respect the joining clause in table_1 for left join you could check the missing match checking for null value (eg using coalesce of ifnull or similar depending on your db features) and for others number on comparision you should manage the null value as 0

    Select a.name, A.cable_leng, a.geom, a.fid
     , case when  a.cable_leng <> coalesce(B.cable_leng,0)
            then 'Cable Name and Length modified'
            when a.name <> coalesce(B.name, '')  then 'Name'
            when a.cable_leng <> coalesce(B.cable_leng,0) then 'Cable Length Modified'
            when b.name is null then 'Deleted Cable'
       end Cables
      , case when a.geom <> B.geom then 'Geometry Modified'
            when a.geom = B.geom then 'No Geometry Changes'
       end Geometry
  from table_1 a
  left  join table_2 b  on a.fid = b.fid

Can i display the extra or missing rows in Table B when only a left or inside SQL join is currently available in QGIS

1 Answers