• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

How should the query be for multiple criteria?

 
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am now trying to use a servlet to query a joined table where there is tutor id and subjectNames.

However, I do not know if my mind is overworked or what I just can't quite get the idea how to write the right query.

So, a user will search thru a list of subjects and then my aim is to obtain the tutor_id with the subjectNames the user want.

So, I thought of using HashMap<String, ArrayList<String>>tutorSubject{

And then I create the query

String[] subjectNames = getParameterValues



I wonder if I am in the right direction.

My mind is now very blank and eyes very pain.

And if I am going to add in more criteria like the location, how should I do it, if I have a table with the tutor details including the location.

I am like very lost now.

Hope to get some tips and helps here.

Many thanks.
 
Sheriff
Posts: 7108
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would think you're trying to build an SQL query with a WHERE clause something like this:

But you need to change the "something" and "somethingElse" into the indices of your array subjectNames, so

But this has two problems: you does know how long the array subjectNames is, and building an SQL query like this (concatenating strings) leaves you open to SQL injection attacks.  So I think you need to build the WHERE clause something like this:

Then using a prepared statement, add the subjectNames indices to the statement.

Does this make sense to you?  I'll try to add code in another reply if I have time.
 
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wouldn't that be:
subjectName = ? OR subjectName = ? etc

Which would be better done as:
subjectName IN (?,?,? etc)

Though you would need to build that (?,?,? etc) up based on the number of things in the list.
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Wouldn't that be:
subjectName = ? OR subjectName = ? etc

Which would be better done as:
subjectName IN (?,?,? etc)

Though you would need to build that (?,?,? etc) up based on the number of things in the list.



I can’t do that since each time the subjectNames will change. Right?
 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why should the subjectNames changing be a problem?

I expect the numbero f subjects to vary, but that's why I said that you would have to build up the set of '?'s in the query:

 
Knute Snortum
Sheriff
Posts: 7108
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Wouldn't that be:
subjectName = ? OR subjectName = ? etc


You're quite right.
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Why should the subjectNames changing be a problem?

I expect the numbero f subjects to vary, but that's why I said that you would have to build up the set of '?'s in the query:



OK. Dave.  You are right.

But, my problem now is that after getting the resultSets out I need to further 'filter' to say find the tutor address so I may need to use inner join.

In this case, do I make 2 separate sql or is it possible to combine everything into 1 sql ?
 
Knute Snortum
Sheriff
Posts: 7108
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should be able to do it all in one query, but it could get complex.  It would look something like this:
(I believe I have my ANDs and ORs correct this time.)
 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Try and do it as one query.
In general it's best to get the database to do the filtering as that's the sort of thing it's designed to do.
It's likely to be better at it than code you would write.

Not that finding the tutor address is filtering.
As you said, it's joining.
 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT tutor_id FROM tutor_subject WHERE subjectName =' " + subjectparameter+"'";
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Sagar Jaybhay wrote:SELECT tutor_id FROM tutor_subject WHERE subjectName =' " + subjectparameter+"'";



That's not what I am looking for.

Why you are not using IN clause ?
 
Knute Snortum
Sheriff
Posts: 7108
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Sagar Jaybhay wrote:SELECT tutor_id FROM tutor_subject WHERE subjectName =' " + subjectparameter+"'";


Concatenating strings to create a query like that is a bad idea in general.  It leaves you open to SQL injection attacks.  Use a prepared statement instead.
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Try and do it as one query.
In general it's best to get the database to do the filtering as that's the sort of thing it's designed to do.
It's likely to be better at it than code you would write.

Not that finding the tutor address is filtering.
As you said, it's joining.



Hi Dave,

I just realised that I am running a problem because my tutor_id in my tutor_subject is not a primary key since it has more than one similiar tutor_id.

Now, I need to use tutor_id to reference the tutor table tutor_id.

How should I change the schema in this case to make the referencing work ?
 
Knute Snortum
Sheriff
Posts: 7108
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So what is the primary key in the tutor_subject table?
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:So what is the primary key in the tutor_subject table?



There is no primary key. At first, I thought it is tutor_id but since it can be more than 1 similar tutor_id, then it can’t be primary key right?
 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would expect it to be (based on the name) tutor_id and subject_id.
Or maybe tutor_id and subject, if they're just a name.
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:I would expect it to be (based on the name) tutor_id and subject_id.
Or maybe tutor_id and subject, if they're just a name.



So, meaning tutor_id can still be made Primary key even though it contains repetitive value? Does it work in all kind of database?
 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No, both columns are the primary key.  It's a composite.
You can't have a primary key which is not unique.
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:No, both columns are the primary key.  It's a composite.
You can't have a primary key which is not unique.



So, how do I make tutor_id a Primary Key then? I have to use a surrogate key right, for my case?
 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You don't make tutor_id a primary key for the tutor_subject table.
You make tutor_id and subject_id the primary key.  It's a composite.

How that works for the db you are using you will have to look up.
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:You don't make tutor_id a primary key for the tutor_subject table.
You make tutor_id and subject_id the primary key.  It's a composite.

How that works for the db you are using you will have to look up.



I am not doubting you but I have done abit of research and I found out that one should avoid composite key as much as possible. So, what is the right way forward?
 
Knute Snortum
Sheriff
Posts: 7108
184
Eclipse IDE Postgres Database VI Editor Chrome Java Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did it say why you should avoid composite keys?  I've been out of the workforce for several years, but why I was coding we used composite keys all the time.  Maybe it's fallen out of favor.
 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
For join tables it's the only way to ensure that you only have one of each combination.
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Knute Snortum wrote:Did it say why you should avoid composite keys?  I've been out of the workforce for several years, but why I was coding we used composite keys all the time.  Maybe it's fallen out of favor.



Yes.  I saw it in one of the articles.

However, I did some more research again after reading your post and found that you are right.  

Also, I read up and understand that there is a key(I think it is surrogate key) which runs on serial number so you don't name it as Primary Key.

I need advice for my case where the tutor_id cannot be unique since it is a many to many relationship.  In my join table, if I were to make the tutor_id, subjectName composite key, do I make the reference foreign key as Serial Number key and not Primary key?

Hope to have your view on this.

Thanks.
 
Dave Tolls
Rancher
Posts: 4576
47
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm lost now.
What's the Serial Number key?

Your join table should join the primary key of the tutor table to the primary key of the subject table.
Those two columns in the join table are that table's primary key.
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:I'm lost now.
What's the Serial Number key?

Your join table should join the primary key of the tutor table to the primary key of the subject table.
Those two columns in the join table are that table's primary key.



Hi Dave,

It is like a surrogate key.  Because, I was thinking if I were to use tutor_id as Primary key in my tutor table, and since it is not Unique, it will create problem later.

So, it seems it is ok to use tutor_id as Primary key and reference my fk - tutor_subject(tutor_id, subjectName) ?

Do you mind let me know the below sqls is it correct based on tutor_id as Primary key at tutor table?


Alter table tutor_subject add constraint fk_constraint_t foreign key(tutor_id) references tutor_subject(tutor_id, subjectName)
Alter table tutor add constraint fk_constraint_ts foreign key tutor_subject(tutor_id, subjectName)

Do let me know if I have missed out anything...

Thanks.

 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
tutor_id is not unique?
So more than one tutor can have the same id?

OK...then tutor_id is not the primary key on the tutor table, so you need to use whatever the primary key is.

Your naming is a bit confusing then.
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:tutor_id is not unique?
So more than one tutor can have the same id?

OK...then tutor_id is not the primary key on the tutor table, so you need to use whatever the primary key is.

Your naming is a bit confusing then.



Sorry Dave. Apparently I was the one that cause all the confusion. It is unique since I am using it as a Primary key.

I presume my sql is correct since there is no comment about that part?
 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I've already commented on the SQL, and how it should be an IN.
SELECT tutor_id, subjectName FROM tutor_subject WHERE subjectName IN (need to build up the correct number of '?' in here)
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:I've already commented on the SQL, and how it should be an IN.
SELECT tutor_id, subjectName FROM tutor_subject WHERE subjectName IN (need to build up the correct number of '?' in here)



But, I didn’t ask about that one cos it was so Long ago about that comment why would I want to bring it up again?

I think it’s ok if you are not willing to comment about my latest sql...
 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry, I completely missed those!



ALTER TABLE tutor_subject ADD CONSTRAINT fk_constraint_t FOREIGN KEY(tutor_id) REFERENCES tutor_subject(tutor_id, subjectName)


The foreign key is against the tutor table.
That looks like you are defining it against itself...and even then I'm not sure that would actually execute, would it, as it says the tutor_id references a tutor_id and subject_name?


ALTER TABLE tutor ADD CONSTRAINT fk_constraint_ts FOREIGN KEY tutor_subject(tutor_id, subjectName)

And this one seems to be missing anything to say what is being mapped.

So..first off.
The foreign key is defined on the table where the key is foreign, ie the "master" is in another table.
So tutor_subject has a foreign key in the form of the tutor_id which references the tutor_id on the  tutor table.

The tutor table has no foreign key, at least in this relationship.

Finally, the tutor_subject table needs a primary key, consisting of the tutor_id and the subject_name, since I assume there is no subject table?
 
tangara goh
Ranch Hand
Posts: 600
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Dave Tolls wrote:Sorry, I completely missed those!



ALTER TABLE tutor_subject ADD CONSTRAINT fk_constraint_t FOREIGN KEY(tutor_id) REFERENCES tutor_subject(tutor_id, subjectName)


The foreign key is against the tutor table.
That looks like you are defining it against itself...and even then I'm not sure that would actually execute, would it, as it says the tutor_id references a tutor_id and subject_name?


ALTER TABLE tutor ADD CONSTRAINT fk_constraint_ts FOREIGN KEY tutor_subject(tutor_id, subjectName)

And this one seems to be missing anything to say what is being mapped.

So..first off.
The foreign key is defined on the table where the key is foreign, ie the "master" is in another table.
So tutor_subject has a foreign key in the form of the tutor_id which references the tutor_id on the  tutor table.

The tutor table has no foreign key, at least in this relationship.

Finally, the tutor_subject table needs a primary key, consisting of the tutor_id and the subject_name, since I assume there is no subject table?



Hi Dave,

Thanks so much for coming back with a reply.

I would like to know if we want to execute a query, even if we don't set the FK or composite Key constraint, the query will still work right ?

Kindly confirm about this part.

Thanks again for your help.

Really and greatly appreciate it.
 
Dave Tolls
Rancher
Posts: 4576
47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It will work, but it will be slower as the DB can't make any assumptions (a FK implies that there is a row on the other table), and has fewer indexes to work with.
 
I brought this back from the farm where they grow the tiny ads:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic