Finding rows where one field is equal and another differs



  • Using PostgreSQL 14.

    I have two tables with sites, timestamps and some other information. I want to get all rows in table B having a site in table A but another timestamp. All rows in table A are also in table B and there are no duplicate with same site and date, so the situation is like this:

    Table A

    site | date
    ------------------
    A    | 2022-01-01
    A    | 2022-02-01
    B    | 2022-01-01
    B    | 2022-02-02
    C    | 2022-01-03
    C    | 2022-02-01
    

    Table B

    site | date
    ------------------
    A    | 2022-01-01
    A    | 2022-02-01
    A    | 2022-02-02
    B    | 2022-01-01
    B    | 2022-02-02
    C    | 2022-01-03
    C    | 2022-02-01
    C    | 2022-03-01
    

    I want to run a query that gives

    site | date
    ------------------
    A    | 2022-02-02
    C    | 2022-03-01
    

    I tried to run

    select B.site,B.date from B left join A on A.site = B.site where A.date != B.date;
    

    But of cource gave me all A and C rows.

    How do I say in SQL: Give me rows with dates that does not exist for a given station in the other table?

    Both tables are around 1000 rows, so a slow query (within reasonable limits) is no disaster.



  • You can use NOT EXISTS:

    SELECT b.site,b.date
    FROM table_b b  
    WHERE NOT EXISTS (SELECT 1 
                      FROM table_a a  
                      WHERE a.site=b.site  
                      AND b.date = a.date
                      );
    

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7fed48443f4790e1b0076aeff4930aa9

    Or you could use LEFT JOIN,

    SELECT b.site,b.date
    FROM table_b b
    LEFT JOIN table_a a  ON  b.site=a.site and b.date = a.date
    WHERE a.site IS NULL;
    

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=d2d62fc4a1d4650cfcff49fda611342a


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2