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
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.
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
Source: Stackoverflow.com