This week's giveaway is in the Programmer Certification (OCPJP) forum.
We're giving away four copies of Java Mock Exams (software) and have David Mayer on-line!
See this thread for details.
Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

AND/OR

 
Mallika Kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
I'm creating a query based on two values entered by the user. If only the first value is entered, I'm creating a query based on that. If only the second value is entered, I'm getting the appropriate query. But when both are entered, I'm creating a query that says
select a.colA,b.colB,c.colD,d.colD from tableA a,tableB b,tableC c,tableD d where a.colA like ? AND a.colB like ?;
In the prepared statement, I'm passing the values for the colA and colB as "%"+colAvalue+"%" and "%"+colBvalue+"%".
So, this query gets rows if both the conditions match. But if I try to make it OR and run the query, I get a join,a Cartesian join,with A*B*C*D number of rows (with A rows in tableA,B rows in tableB,C rows in tableC and D rows in tableD). How can I modify the select statement so that I can achieve an OR operator and not AND ?
Am I missing something here ? Any suggestions will really be very helpful.
Thanks a lot.
 
Thomas Paul
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to specify join criteria for each of the 4 tables you are including in your SQL. Something like:
select a.colA,b.colB,c.colD,d.colD from tableA a,tableB b,tableC c,tableD d where a.colA = b.colB and a.colA = c.colD and a.colA = d.colD and (a.colA like ? or a.colB like ?);
 
Mallika Kumar
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a TON THOMAS, it worked. Gosh, its great to see responses to all questions here.
 
Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!