SELECT values excluding values from other SELECT

Question

I have a select that brings me some primary keys. I need to SELECT all values from other table that are not listed in the previous query. How can I do it?

I have been trying:

SELECT id
FROM tab1,
   ,(SELECT id...
     WHERE LIKE '%abc%'
    ) AS result
WHERE result != tab1.id;

But didn't work, it brings me also the data from the subquery. I'm using PostgreSQL.

`result` is alias for your inner query result as a table name and selected columns will be the columns of that table. So you treat it as a table only. Then you need to do `result.id` to use particular column in comparing. — Bharadwaj, Jul 02 '14 at 13:42
This is my SQL now: SELECT distinct(query1.codcli) FROM tbcli as query1 RIGHT OUTER JOIN (SELECT tab2.codcli FROM tbsrv as srv INNER JOIN tbsrvcli as srvcli ON srv.codserv = srvcli.codserv INNER JOIN tbcli as tab2 ON tab2.codcli = srvcli.codcli where servico not like '%Sonic%') as query2 ON query2.codcli = query1.codcli; But still not working — Erik Figueiredo, Jul 02 '14 at 14:05

score 1 · Answer 1 · answered Jul 02 '14 at 13:44

1

SELECT tab1.id
FROM tab1 LEFT OUTER JOIN
    (SELECT id
     FROM ...
     WHERE LIKE '%abc%'
     ) AS result
ON result.ID = tab1.id
where result.ID is null;

answered Jul 02 '14 at 13:44

vasja

4,732
13
15

score 1 · Answer 2 · answered Jul 02 '14 at 13:51

1

SELECT id
FROM tab1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM tab2 t2
    WHERE t2.id = t1.id 
    -- AND t2.name LIKE '%abc%'
    )
    ;

answered Jul 02 '14 at 13:51

wildplasser

43,142
8
66
109

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – Hatem Alimam Jul 02 '14 at 14:19
1

This query is similar to the query in the answer given by RSB. – wildplasser Jul 02 '14 at 15:55

score 1 · Accepted Answer · answered Jul 02 '14 at 14:08

1

You can try this,

SELECT * from table1 WHERE id NOT IN (SELECT id from table2 where );

where "id" will be common coloumn in both tables i.e in table1 and table2. and will be the condition on whatever basis you need to fetch records from table2 in inner query.

answered Jul 02 '14 at 14:08

RSB

359
5
10

1

`NOT IN` is the *least* advisable among [a number of possible techniques in Postgres.](http://stackoverflow.com/questions/19363481/select-rows-which-are-not-present-in-other-table/19364694#19364694) In particular, it does not work well with `NULL` values. – Erwin Brandstetter Jul 02 '14 at 16:21

SELECT values excluding values from other SELECT

3 Answers3