Solved

Query statement-HELP

Posted on 2003-11-28
13
224 Views
Last Modified: 2010-04-01
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

0
Comment
Question by:Jasbir21
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 14

Expert Comment

by:kennethxu
ID: 9837952
>> 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
 

Author Comment

by:Jasbir21
ID: 9837982
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 9838112
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:kennethxu
ID: 9838138
I guess I was still sleeping. I meant to
let us know the table structure/relationship and your final goal of the result.
0
 

Author Comment

by:Jasbir21
ID: 9838181
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
 
LVL 14

Accepted Solution

by:
kennethxu earned 100 total points
ID: 9838347
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
 

Author Comment

by:Jasbir21
ID: 9840690
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
 

Author Comment

by:Jasbir21
ID: 9840901
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
 

Author Comment

by:Jasbir21
ID: 9840911
...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
 
LVL 14

Expert Comment

by:kennethxu
ID: 9841091
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 9841132
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
 

Author Comment

by:Jasbir21
ID: 9841261
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 9841312
>> 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
AlarmClock Challenge 35 157
print map entry 34 79
login jsp example 24 105
import as existing maven project 3 78
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Suggested Courses

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question