Solved

Query statement-HELP

Posted on 2003-11-28
13
218 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
change size of out.write buffer 1 80
trouble with PDF encryption using itext-1.3.1 17 77
lessBy10  challenge 15 89
countXY challenge 28 117
Short answer to this question: there is no effective WiFi manager in iOS devices as seen in Windows WiFi or Macbook OSx WiFi management, but this article will try and provide some amicable solutions to better suite your needs.
In this article, I will show you HOW TO: Perform a Physical to Virtual (P2V) Conversion the easy way from a computer backup (image).
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now