PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to compare a subset of the Column of a table with the column of another table in SQL?

Table 1:

Name    Address   Height    Weight
X.        y         z.        p
G.        H.        I         J
Q         W.        E.        R

Table 2:

Name    Income     Tax    
X.        y         z
A         B         C

Trying compare the rows between the two tables and get the names that appear only in table 1 and not in table 2 as well? However, I am only trying to compare only certain rows from table_1, for example – the rows which contain Height= I and E do not need to be on the count.

Here is what I have so far but I am getting a syntax error:

SELECT count(DISTINCT "Name") FROM table_1 WHERE table_1.Height NOT LIKE ANY CONCAT('%', {'I', 'E'} '%')

WHERE NOT EXISTS (

    SELECT DISTINCT UPPER("Name") FROM table_2 WHERE table_2."Income" = 'y' AND UPPER(table_2."Name") LIKE CONCAT('%', UPPER(table_1."Name"), '%')

)

syntax error at or near "WHERE"

The result of the query should be 1 as there is a count() but without count it should look like

Name    Address   Height    Weight
X.        y         z.        p
Tags:

3

Answers


  1. 0

    Sorry,for me it is not very clear what you want to achieve, but based on this " get the names that appear only in table 1 and not in table 2 as well? However, I am only trying to compare only certain rows from table_1, for example – the rows which contain Height= I and E do not need to be on the count"

    WITH TABLE_1(NAME,ADDRESS,HEIGHT,WEIGHT) AS
(
  SELECT 'X','Y','Z','P' UNION ALL 
  SELECT 'G','H','I','J' UNION ALL 
  SELECT 'Q','W','E','R'
),
TABLE_2(NAME,INCOME,TAX) AS
(
  SELECT 'X','Y','Z' UNION ALL 
  SELECT 'A','B','C'
)
SELECT COUNT(*)AS CNTT FROM
(
   SELECT T1.NAME
   FROM TABLE_1 AS T1
   WHERE T1.HEIGHT NOT IN('E','I')
     EXCEPT 
   SELECT T2.NAME
  FROM TABLE_2 AS T2
)AS X
    Login or Signup to reply.
  2. 0

    Can you left join table 2 to table 1 and filter for where name in table 2 is null?
    ie:

    SELECT table1.*
FROM 
  (SELECT * 
   FROM table1 
   WHERE Height NOT LIKE '%E%' 
     and Height NOT LIKE '%I%') table1
LEFT JOIN table2
  ON table1.name = table2.name
WHERE table2.name is null
    Login or Signup to reply.
  3. 0

    Here’s what I cam up with. You do know that this will have no results…

    create table Table_1
    (Name varchar(2)
    ,Address varchar(2)
    ,Height varchar(2)
    ,Weight varchar(2)); commit;
    –=================================================================================

    insert into Table_1 (Name, Address, Height, Weight) values (‘X.’, ‘y’, ‘z.’, ‘p’); commit;
    insert into Table_1 (Name, Address, Height, Weight) values (‘G.’, ‘H.’, ‘I’, ‘J’); commit;
    insert into Table_1 (Name, Address, Height, Weight) values (‘Q’, ‘W.’, ‘E.’, ‘R’); commit;
    — select * from Table_1
    –=================================================================================
    –=================================================================================

    create table Table_2
    (Name varchar(2)
    ,Income varchar(2)
    ,Tax varchar(2)); commit;
    –=================================================================================

    insert into Table_2 (Name, Income, Tax) values (‘X.’, ‘y’, ‘z’); commit;
    insert into Table_2 (Name, Income, Tax) values (‘A’, ‘B’, ‘C’); commit;
    — select * from Table_2
    –=================================================================================
    –=================================================================================

    select distinct name from Table_1 t1 where t1.height not in (‘I’,’E.’) and t1.Name not in (select distinct Name from Table_2);

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search