Hello,
I would like to write sql which would search over mutiple rows without needing to write dynamic SQL statement.
create table table_a (id number, title varchar2(100));
alter table table_a add constraint uk_table_a unique (id);
begin
insert into table_a (id, title) values (1, 'House A');
insert into table_a (id, title) values (2, 'House B');
insert into table_a (id, title) values (3, 'House C');
commit;
end;
/
create table table_b(ref_id number, name varchar2(35), value varchar2(250));
alter table table_b add constraint fk_table_b foreign key (ref_id) references table_a(id);
begin
insert into table_b (ref_id, name, value) values (1, 'Pool', 'No');
insert into table_b (ref_id, name, value) values (2, 'Pool', 'Yes');
insert into table_b (ref_id, name, value) values (3, 'Pool', 'Yes');
insert into table_b (ref_id, name, value) values (1, 'Bedroom', '1');
insert into table_b (ref_id, name, value) values (2, 'Bedroom', '2');
insert into table_b (ref_id, name, value) values (3, 'Bedroom', '3');
insert into table_b (ref_id, name, value) values (1, 'Created', '2000-12-15');
insert into table_b (ref_id, name, value) values (2, 'Created', '1995-06-04');
insert into table_b (ref_id, name, value) values (3, 'Created', '2006-07-20');
commit;
end;
/
How would my SQL work? I would like to add many search criteria over table B and would get primary record from table A.
Example:
WHERE: Pool: YES AND Bedroom > 1
RETURN: House B, House C
WHERE: Created between 1996-01-01 and 01-01-2001
RETURN: House A
Any idea how I would achieve that?
Regards, Igor