How best to approach nested queries?
Hi,
Could someone recommend the best way of dealing with complex sql queries, I have played around with making it one long sql string, although this is returning a value twice which only appears once.
String query ="select T1.Subject, T2.Object from Test6 T1 join Test6 T2 where T1.Subject = T2.Subject and T2.Predicate = 'code' and T1.Object ='Spring'";
I now need to try playing with multiple statements, but am unsure how to progress...
[508 byte] By [
jjloop] at [2007-9-26 3:13:56]
It sounds like you are returning the Cartesian Product(all possible COMBINATIONS of rows returned when a join condition is invalid or omitted completely) of your query or the relationship of your tables is a many to many relationship. I would suggest that you try to solve this problem first. But if you want the workaround, I guess you could use statements to create inner queries:
String query 1 = "select id, name from emp where name like 'Simpson'";
String innerquery = "select id, name from (" + query 1 +") emp1 where id < 10000 ";
String innerquery2 = "select id, name from (" + innerquery + ") emp2 where id > 50";
...etc
The innerquery creates a subset of results for the query to select from (as if it were a table).
I think this is what you were getting at
Jamie
The following doesn't work?
String query = "select T1.Subject, T2.Object"
+ " from Test6 T1, Test6 T2"
+ " where T1.Subject = T2.Subject"
+ " and T2.Predicate = 'code' "
+ " and T1.Object ='Spring'";
.
If that doesn't work then I would expect that your dataset is more complicated and you need to describe the rules more. (Although a 'group by' might fix it.)
