Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query statement-HELP

Posted on 2003-11-28
13
Medium Priority
?
231 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this article, I’ll show how research, determination, and use of modern technology helped me solve a DNA mystery.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

722 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