Query statement-HELP

hi,
 I got a query statment,


String query1="Select skill,email from table1 username='"+vol.getUsername()+"'";

rs2=stmt1.executeQuery(query1);



String query2="Select skillid from project where ";

rs3=stmt2.executeQuery(query2);

boolean any=rs2.next();
boolean any1=rs3.next();

I think i did wrong coz not working,

but this is what i want:

First:Select skillid,email from table1 where username='vol.getUsername()';

Then compare it with the second table project and see whether the required skillis are needed,if yes send email.
I know send email part a bit, my current problem is that how to write query..
i mean if the

Pls help,
thnaks

Jasbir21Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kennethxuCommented:
>> String query1="Select skill,email from table1 username='"+vol.getUsername()+"'";
you are missing WHERE keyword in the sql statement.

>> String query2="Select skillid from project where ";
you are missing condition after where.
0
Jasbir21Author Commented:
String query1="Select skill,email from table1 where username='"+vol.getUsername()+"'";

Sorry ,it was typo,error,

my problem is with the second string

="Select skillid from project where ??? ";


I mean ,it needs to be like for example:

Lets say user A got skill not null-Teacher,Nurse and etc.

The second query statemanet need to be queried for skill of Teacher,Nurse and etc..

Thansk


0
kennethxuCommented:
let us the structure of the 2 tables and let us know if final goal. and what's the diff between skill and skillid?
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

kennethxuCommented:
I guess I was still sleeping. I meant to
let us know the table structure/relationship and your final goal of the result.
0
Jasbir21Author Commented:
i think table structure is lilke this:

For example table user:
username,password,verifypassword.....email,skill
(A lot of information about user)

Now i have a table project:
projectname,skill,start,end...

Lets say a user has a skill of Teacher,Nurse and an organization is looking for it,
Forexample:
Project id      Duration                     Skill
L ittle lamb  12Jan2003 2Feb 2003 Teacher,Doctor
KKK            5 Feb       10Feb         Teacher,Nurse

It is like sending confirmaition email, that your required skills are needed,

of course i need to see the project duration and etc, but i can't get to query , i think project duration i think later.
I mean this system will match required skill with requested skill

PLs note:No difference bet skill and skillid
0
kennethxuCommented:
what will the skill column looks like in user table, something like Doctor,Nurse

It might be too late but I would still try to make comment on your database structure, because this is important and make you rest of coding much easier:

you table are not normalized which make you difficult to query the result and also waste of database space. for example, I would have something like this:

Tables:
skills( skillid int, skill varchar ) -- describe all the skills used in the system.
projects( projectid int, projectname varchar, startdate date, enddate date ) -- project detail table
projectskills( projectid int, skillid int) -- required skills in project
users( userid, username, ...) -- user detail.
userskills( userid, skillid int) -- skills a user has.

Data example:
skills
skillid, skill
1        Teacher
2         Doctor
3         Nurse

projects
projectid, projectname, startdate,    enddate
1             L ittle lamb    12Jan         2Feb 2003
2             KKK               5 Feb         10Feb

users
userid, username, ...
u1       Scott Tiger
u2       John Smith

projectskills
projectid, skillid
1             1
1             2
2             1
2             3

userskills
userid, skillid
u1       1
u2       2
u2       3

the you can query it by one sql statement:

select projects.projectid, projects.projectname, skills.skillname
from users, userskills, projectskills, projects, skills
where users.userid = userskills.userid
and userskills.skillid = projectskills.skillid
and projectskills.projectid = projects.projectid
and projectskills.skillid = skills.skillid
and users.userid = 'u2'

you can simplify the structure by eliminating userskills table if every user has only one skill.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jasbir21Author Commented:
hi,
  that means when skill enters table it should be like this :
Userid Skillid
u1        1
u1        2


My problems is that in the form when the user enter the column for skills, the skills are entered in a textarea, so many skills can be entered like this:
Skills:
<textarea type="text" name="skill" rows="5"  cols="15" wrap readonly>
</textarea>
So, when skills get entered , it is separated by comma(,)
eg, Teacher,Nurse,

Does it mean i need to change the form,pls tell, i am very confuse.I mean how  skills entered in a table separated by comma go different column then,pls help me on this
0
Jasbir21Author Commented:
I think i understand, use tokennizer,the database structure is the problem not the form i think, would be posting another question on it,pls look at it too.

thanks
0
Jasbir21Author Commented:
...because of the database structure, i guess i was facing a lot of problems for the few days , thanks a lot.
....think understood about it as well about the tokennizer and the form ..
thanks
Regards,
0
kennethxuCommented:
Hi Jasbir, It really depend on how you let you user enter skills. If you let user enter in free text, user could have typed pediatrics instead of doctor which make difficult to match.

if you want to use comma seperated string and keep your table structure. this might help:

select skill from usertable
tokenizer the skill string
for each userskill select project from projecttable where skill like '%userskill%'

HTH
0
kennethxuCommented:
BTW, be aware that using like condition to search is going to be quite slow and no index could help.

let me know if you have further enquires.
0
Jasbir21Author Commented:
hi,
  It was my fault, i guess the problem was with the table,i have changed it. BUt now i am changing using the select query type.

I did a query using the select,  for output : i mean using "select...and "but i am getting error
It is at :
I think not fair if i ask here so i post new question at there:

http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20811369.html

Pls see.
thanks
0
kennethxuCommented:
>> I think not fair if i ask here so i post new question at there:
it's alright. i'm happy to answer closely related questions in one thread.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.