Solved

Query statement-HELP

Posted on 2003-11-28
13
221 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

For months I had no idea how to 'discover' the IP address of the other end of a link (without asking someone who knows), and it drove me batty. Think about it. You can't use Cisco Discovery Protocol (CDP) because it's not implemented on the ASAs.…
An article on effective troubleshooting
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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