I've got two postgresql tables:
table name column names
----------- ------------------------
login_log ip | etc.
ip_location ip | location | hostname | etc.
I want to get every IP address from login_log
which doesn't have a row in ip_location
.
I tried this query but it throws a syntax error.
SELECT login_log.ip
FROM login_log
WHERE NOT EXIST (SELECT ip_location.ip
FROM ip_location
WHERE login_log.ip = ip_location.ip)
ERROR: syntax error at or near "SELECT" LINE 3: WHERE NOT EXIST (SELECT ip_location.ip`
I'm also wondering if this query (with adjustments to make it work) is the best performing query for this purpose.
This question is related to
sql
postgresql
null
left-join
exists
A.) The command is NOT EXISTS, you're missing the 'S'.
B.) Use NOT IN instead
SELECT ip
FROM login_log
WHERE ip NOT IN (
SELECT ip
FROM ip_location
)
;
this can also be tried...
SELECT l.ip, tbl2.ip as ip2, tbl2.hostname
FROM login_log l
LEFT JOIN (SELECT ip_location.ip, ip_location.hostname
FROM ip_location
WHERE ip_location.ip is null)tbl2
SELECT *
FROM testcases1 t
WHERE NOT EXISTS (
SELECT 1
FROM executions1 i
WHERE t.tc_id = i.tc_id and t.pro_id=i.pro_id and pro_id=7 and version_id=5
) and pro_id=7 ;
Here testcases1 table contains all datas and executions1 table contains some data among testcases1 table. I am retrieving only the datas which are not present in exections1 table. ( and even I am giving some conditions inside that you can also give.) specify condition which should not be there in retrieving data should be inside brackets.
Source: Stackoverflow.com