Returning a value when a column contains at least one match

Question

Edit: Sorry to not mention before this is on a postgres 8.3 implementation.

I have a table of four columns that was built today table.

Source_IP, Destination_IP, user_type, ut_bool

The user_type column continually gets new entries and so I want to compare it to a table o historical entries to see if it is new or not for the day in question. The source_ip, destination_Ip can be considered the primary key

10.1.1.2, 30.30.30.1, type1, 0
10.1.1.2, 30.30.30.1, type4, 1
10.1.1.2, 30.30.30.4, type5, 0
10.1.1.2, 30.30.30.4, type3, 0
10.1.1.2, 30.30.50.9, type2, 0
10.1.1.4, 30.30.30.4, type4, 0
10.1.1.4, 30.30.30.4, type3, 1

I am having trouble returning a 1 to a column for a given group of (source_ip, destination_ip) pairs if a least one source_ip,destination_ip, user_type pair has a 1 next to it so for example I want to get

10.1.1.2, 30.30.30.1, 1
10.1.1.2, 30.30.30.4, 0
10.1.1.4, 30.30.30.4, 1

I am not sure how to use the exists statement correctly.

How do I fix the following query?

select source_ip, destination_ip,
(
select
case when exists 
(select true from table
where ut_bool =1)
then '1'
else '0' end
) as ut_new
from
table;

My query keeps returning because I am not using the exists statement correctly:

10.1.1.2, 30.30.30.1, 0
10.1.1.2, 30.30.30.4, 0
10.1.1.4, 30.30.30.4, 0

Answer 1

I'd suggest modification of your SQL statement:

SELECT SOURCE_IP,
  DESTINATION_IP,
  CASE SUM(UT_BOOL)
    WHEN 0
    THEN 0
    ELSE 1
  END AS UT_NEW
FROM test_table_name
GROUP BY SOURCE_IP,
  DESTINATION_IP;

Executed on your test data returns:

10.1.1.2    30.30.50.9  0
10.1.1.2    30.30.30.1  1
10.1.1.2    30.30.30.4  0
10.1.1.4    30.30.30.4  1

Tested and working on Oracle and Postgres