CODEWITHSUNDEEP
sqlpostgresqlselect
Super_user_two
Super_user_two

Reputation: 81

PostgreSQL selecting each row from two temporary tables and comparing their values

I have two tables, foo and bar that both have the same schema and rowtype

Table1

name varchar
id int
age int

Foo contains 300 records, and Bar contains 100 records. Is there a select statement that will let me compare every row in Foo with every row in bar to determine if foo.age > bar.age? So if it was a for loop in java it would look something like this:

for(int i = 0; i < Foo.length; i++){
    for(int j = 0; j < Bar.length; j++){
       //Select here if foo[i].age > bar[j].age
    }
}

EDIT FOLLOW UP QUESTION:

If I have a temporary table called 'foobar', with the same schema as in 'Table 1' How can I insert the rows into foobar that are only part of foo into the temporary table. For example in java:

List list = new LinkedList<Table1>
for(int i = 0; i < Foo.length; i++){
    for(int j = 0; j < Bar.length; j++){
       if(foo[i].age > bar[j].age){
            list.add(foo[i]);
       }
    }
}

Upvotes: 0

Views: 511

Answers (2)

Shivam Dhoot
Shivam Dhoot

Reputation: 151

select * from foo cross join bar where foo.age > bar.age

will do this but i wonder why do you want to compare one entry to each and every entry in other table.

Upvotes: 0

user330315
user330315

Reputation: 

select *
from foo
  cross join bar
where foo.age > bar.age

Upvotes: 1

Related Questions