Solved

Help-Query statement !!!

Posted on 2003-11-30
28
303 Views
Last Modified: 2010-04-01
hi,
  i need help for query.
How to do a query that ,
first checks whether notify is yes,


and if yes, it needs to check whether the info is in the table and if the info not in table then only send email.

The tables are:
users(username,notify...)

userskills(username,skill)

projects(username,projectid,skill)

useremail(username,userid,projectid,skill)-lets say email, is sent to user, teh username of user,userid of organization,projectid or organization and skill is stored.

It goes like this first see whether the users table in notify is yes,
if yes see the skill in the userskill tables and if the skills matches the project skill, check whether email is sent (check whether the skills alreday stored in useremail, if not then sent email and insert into useremail.


I know this part : but not how to query useremail part..

Select userskills.skill,projects.projectid from userskills,projects,users where  userskills.username=users.username and projects.skill=userskills.skill and users.notify='yes' and username=+'vol.getUser()'"+;

I don ';t know the rest...



Is there such thing like this:
Select userskills.skill,projects.projectid from userskills,users,projects,useremail where users.notify='yes' and   userskills.username=users.username and projects.skill=userskills.skill and.... and  username=+'vol.getUser()'"+;







0
Comment
Question by:Jasbir21
  • 13
  • 10
  • 5
28 Comments
 
LVL 6

Expert Comment

by:jarasa
ID: 9849848
Hi

I'm not sure if this is what you want:

SELECT * FROM USEREMAIL WHERE USERMAIL.USERNAME IN (SELECT USERSKILLS.USERNAME FROM USERSKILLS,PROJECTS,USERS WHERE  USERSKILLS.USERNAME=USERS.USERNAME AND USERS.USERNAME=PROJECTS.USERNAME AND PROJECTS.SKILL=USERSKILLS.SKILL AND USERS.NOTIFY='YES');

regards
Javier
0
 

Author Comment

by:Jasbir21
ID: 9850398
hi,
 I am sorry, i might have not made it clear .

i write in long query:

String query="select users.notify from users,useraccount where users.username=useraccount.username and users.username='"+vol.getUsername()+"'";  
statement1=connection.createStatement();
rs1=statement1.executeQuery(query);
boolean any=rs1.next()
//If the notify equals yes, then send email
if(rs1.getstring("notify").equals('yes')){

//See whether the skills matches,if yes send email but.._>
String que="Select projects.projectid,userskills.skill,projects.userid,userskills.username from projects,users,userskills where users.username=userskills.username and projects.skill=userskills.skill and  users.username='"+vol.getUsername()+"'";
rs5=stmt3.executeQuery(que);
if(rs5.next()){

-->only sent email if the info is not in the table useremail
String q="Select useremail.projectid from projects,users,userskills,useremail  where users.username=userskills.username and users.username=useremail.username and useremail.skill=projects.skill and useremail.skill=userskills.skill and useremail.userid=projects.userid and projects.skill=userskills.skill and  users.username='"+vol.getUsername()+"'";
re=stmt4.executeQuery(q);

for the re how to do

if(re query does not exist) is it (!re.next()){


HELP!!

0
 
LVL 6

Expert Comment

by:jarasa
ID: 9850579
Hmm
Wich field make you know when you have to send the mail? ProjectID?? if so, has it to be empty or null ??

Lets see if I got it right, you need to send a mail to everyone in userEmail who don't have ProjectID but has YES in users.notify

Is that OK??

javier
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9851295
assuming your key in the useremal table is projectid, username and skill:

// this will get all projects with matched skill for a user:
String que="Select projects.projectid,userskills.skill,projects.userid,userskills.username from projects,users,userskills where users.username=userskills.username and projects.skill=userskills.skill and  users.username='"+vol.getUsername()+"'";

String q="select 1 from useremail where projectid=? and username=? and skill=?";

PreparedStatment ps = conn.prepareStatement( q );
rs5=stmt3.executeQuery(que);

while(rs5.next()){
  String projectId = rs5.getString( "projectid" ); // can be other types like int
  String username = rs5.getString( "username" );
  String skill = rs5.getString( "skill" );
  ps.setString( 1, projectId );
  ps.setString( 2, username );
  ps.setString( 3, skill );
  re = ps.executeQuery();
  if( rs.next() ) {
    // email already sent
  } else {
    // send email
  }
}
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9851312
in my previous comment:
>>   if( rs.next() ) {
this is not correct, it should be   if( re.next() ) {
BTW, if the data type is integer instead of string, you should be using getInt and setInt instead of getString and setString
0
 

Author Comment

by:Jasbir21
ID: 9858041
hi,

 I have a table called useremail that keeps info on the email sent to user.
Eg
projectid  Skillid username  userid
1               1        u21          little lamb

This is because username 21 can have many skills so if email is sent put in the table.

kennethxu,
  I tried that but table useremail no data insert.
What i need is that if all projects with matched skill for a user, it needs to check useremail whether the username(u1),projectid,skillid,userid of organisation is there.
If no, then only insert in useremail/.

Thansk

0
 
LVL 6

Expert Comment

by:jarasa
ID: 9858956
Hi again.

I think this should work:

Select projects.* from projects, skills, users where ((projects.username=users.username and users.notify="yes") and (projects.username=skills.username and projects.skill=skills.skill)) and projects.username not in (Select useremail.username from useremail, projects, users where (useremail.username=projects.username and useremail.projectid=projects.projectid and useremail.skill=projects.skill) and (useremail.username=users.username and users.notify="yes"))

This will return all the Username, ProjectIds and Skills of those who has to be notify but hasn't been notified already.

If Skill and Projectid are strings then it could be done easier like this:

Select projects.* from projects, skills, users where ((projects.username=users.username and users.notify="yes") and projects.username + projects.skill = skills.username + skills.skill) and projects.username not in (Select useremail.username from useremail, projects, users where (useremail.username + useremail.projectid + useremail.skill = projects.username + projects.projectid + projects.skill) and (useremail.username = users.username and users.notify = "yes"))

regards

Javier
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9859040
I've tested on Access and it work just ok, you just have to make this query and insert the result into the useremail table.
javier
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9859099
Since this sentence works if you don't care about userid field you could make the INSERT-SELECT directly doing this:

INSERT INTO useremail
SELECT projects.*, " " AS userid
FROM projects, skills, users
WHERE ((projects.username=users.username and users.notify="yes") and projects.username + projects.skill = skills.username + skills.skill) and projects.username not in (Select useremail.username from useremail, projects, users where (useremail.username + useremail.projectid + useremail.skill = projects.username + projects.projectid + projects.skill) and (useremail.username = users.username and users.notify = "yes"));

But if you pretend to put the value of userid then won't be a good idea.

Javier
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9859289
Hi again.

One thing I did not mention, is that in the INSERT-SELECT sentence the fields of the SELECT Statement MUST be in the same order and have the same name than the Table that receive them. So you better put it field by fiels istead of using 'projects.*' OK?

INSERT INTO useremail
SELECT projects.username, projects.projectid, projects.skill,  " " AS userid
FROM projects, skills, users
WHERE ((projects.username=users.username and users.notify="yes") and projects.username + projects.skill = skills.username + skills.skill) and projects.username not in (Select useremail.username from useremail, projects, users where (useremail.username + useremail.projectid + useremail.skill = projects.username + projects.projectid + projects.skill) and (useremail.username = users.username and users.notify = "yes"));


javier
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9859425
OPSSS I'm sorry but the one I typed before does not work properly but this one I can asure it:

INSERT INTO useremail
SELECT projects.username AS username, projects.projectid AS projectid, projects.skill AS skill, " " AS userid
FROM projects, skills, users
WHERE ((projects.username=users.username and users.notify="yes") and (projects.username=skills.username and projects.skill=skills.skill)) and projects.username+projects.projectid+projects.skill not in (Select useremail.username+useremail.projectid+useremail.skill from useremail, projects, users where (projects.username + projects.projectid + projects.skill=useremail.username + useremail.projectid + useremail.skill) and (useremail.username = users.username and users.notify = "yes"));


Javier
0
 

Author Comment

by:Jasbir21
ID: 9859447
hi,
  Notification is only sent when user logs in.I guess i am confusing, i am sorry:

this is what i did:

if(rs1.getString("notify").equals("yes"))
{


String que="Select projects.projectid,userskills.skill,projects.userid,userskills.username from projects,userskills where users.username=userskills.username and projects.skill=userskills.skill and  users.username='"+vol.getUsername()+"'";
rs5=stmt3.executeQuery(que);

System.out.println("OOOOO");


do{


String q="select userskills.username from projects,userskills,users ,useremail where useremail.username=users.username and useremail.username=userskills.username and  projects.projectid=useremail.projectid and projects.userid=useremail.userid and projects.skill=useremail.skill and useremail.skill=userskills.skill and userskills.username='"+vol.getUsername()+"'";

re=stmt2.executeQuery(q);

 if( re.next() ) {

System.out.println(re.getString("username"));
System.out.print("Already sent");

//email already sent...

}
else{
send email;
q="insert into useremail(username,userid,projectid,skill)values(?,?,?,?)";
 PreparedStatement  ps=connection.prepareStatement(q);
   ps.setString(1,use);
   ps.setString(2,us);
   ps.setString(3,pro);

ps.setString(4,ski);


   ps.executeUpdate();
....
}

}
while(rs5.next());

}//if

My problem is if once email sent, the next time it is not being ssent
(Meaning the email for skill teacher is sent for u1, but howabout skill nurse for u1???
for example:

userskils table

userid Skill
u1       Teacher
u1        Nurse

projects table
userid     projectid    skill
little lam   1he          Teacher
little lam    1 he          Nurse
...

When the query runs,
this gets inserted in useremail:

 userid     projectid username skill
little lam   1he           u1         Teacher

--->BUT this is not inserted this line with nurse skill
*******
little lam  1he              u1           Nurse


Actually,it first my id is int, but i think i did something, thats why for a while i change to string, for a while it is string.

Pls help
 





0
 

Author Comment

by:Jasbir21
ID: 9859480
Javier, sorry i just saw the new one.I look
thanks
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9859685
I thought the the table Projects was
username
projectid
skill

if it has userid instead username then the query is wrong.

let me see it, one minute.

javier
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Expert Comment

by:jarasa
ID: 9859758
Hi
I don't get it, could you please send the descriptions of your tables if are different than the ones you typed the first time??

>>The tables are:
>>users(username,notify...)
>>userskills(username,skill)
>>projects(username,projectid,skill)
>>useremail(username,userid,projectid,skill)

if not the query I gave has to work it already returns all the mails you have to send for all the users if you want it just for one user then you should use it as a varible.

Javier
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9859837
LOOK THIS IS WHAT I DID CREATE IN ACCESS TO SIMULATE YOUR PROBLEM


USERS TABLE
username      notify
u1            yes
u2            no
u3            yes
u4            yes



PROJECTS TABLE
username      projectid        skill
u1            1            1
u1            1            2
u1            1            3
u2            1            1
u2            1            2
u3            1            2
u3            1            3
u4            1            1
u4            1            2
u4            1            3


SKILLS TABLE
username      skill
u1            1
u1            2
u1            3
u2            1
u2            2
u3            2
u3            3
u4            1
u4            2
u4            3

USEREMAIL TABLE
username      projectid                   skill      userid
u1            1            1      First Mail


AND THIS IS THE RESULT OF THE QUERY
SELECT projects.username AS username, projects.projectid AS projectid, projects.skill AS skill, " " AS userid
FROM projects, skills, users
WHERE ((projects.username=users.username and users.notify="yes") and (projects.username=skills.username and projects.skill=skills.skill)) and projects.username+projects.projectid+projects.skill not in (Select useremail.username+useremail.projectid+useremail.skill from useremail, projects, users where (projects.username + projects.projectid + projects.skill=useremail.username + useremail.projectid + useremail.skill) and (useremail.username = users.username and users.notify = "yes"));

username      projectid                   skill      userid
u1            1            3       
u1            1            2       
u3            1            3       
u3            1            2       
u4            1            3       
u4            1            2       
u4            1            1       

This should be the emails you have to send and insert into useremail. AM I RIGHT???

Javier
0
 
LVL 14

Accepted Solution

by:
kennethxu earned 80 total points
ID: 9860357
<Javier>
kennethxu,
  I tried that but table useremail no data insert.
What i need is that if all projects with matched skill for a user, it needs to check useremail whether the username(u1),projectid,skillid,userid of organisation is there.
If no, then only insert in useremail/.
</Javier>

As I have shown you the logic to determine if a record already exists, you can easliy insert it if one doesn't exist. for example (you need to add the organisation to complete it):
String q="select 1 from useremail where projectid=? and username=? and skill=?";
String sqlInsert="insert into useremail(projectid, username, skill) values( ?,?,?)";

PreparedStatment ps = conn.prepareStatement( q );
PreparedStatment psInsert = conn.prepareStatement( sqlInsert );
rs5=stmt3.executeQuery(que);

while(rs5.next()){
  String projectId = rs5.getString( "projectid" ); // can be other types like int
  String username = rs5.getString( "username" );
  String skill = rs5.getString( "skill" );
  ps.setString( 1, projectId );
  ps.setString( 2, username );
  ps.setString( 3, skill );
  re = ps.executeQuery();
  if( rs.next() ) {
    // email already sent
  } else {
    // insert into it
    psInsert.setString( 1, projectId );
    psInsert.setString( 2, username );
    psInsert.setString( 3, skill );
    psInsert.executeUpdate();
  }
}
0
 

Author Comment

by:Jasbir21
ID: 9863014
Javier, i am very sorry for the late reply, i hope you understand the time difference.
projects(username,projectid,skill)

Actually , the tables are the same, i just change the username in projects to userid, i was scared the two will be confused.





0
 

Author Comment

by:Jasbir21
ID: 9863851
hi,
  I tried that bu t i got the error :

String q="select 1 from useremail where projectid=? and username=? and skill=?";

wrong syntax in mysql,

this is what i did:
if(rs1.getString("notify").equals("yes"))
{

String q="select userskills.username from projects,userskills,users ,useremail where useremail.username=users.username and useremail.username=userskills.username and  projects.projectid=useremail.projectid and projects.userid=useremail.userid and projects.skill=useremail.skill and useremail.skill=userskills.skill and userskills.username='"+vol.getUsername()+"'";


String sqlInsert="insert into useremail(projectid, username, skill,userid) values( ?,?,?,?)";
String que="Select projects.projectid,userskills.skill,projects.userid,userskills.username from projects,userskills,users where users.username=userskills.username and projects.skill=userskills.skill and  users.username='"+vol.getUsername()+"'";

PreparedStatement psInsert = connection.prepareStatement( sqlInsert );
rs5=stmt3.executeQuery(que);

re=stmt2.executeQuery(q);

while(rs5.next()){
  String pro = rs5.getString( "projectid" ); // can be other types like int
  String use = rs5.getString( "username" );
  String ski = rs5.getString( "skill" );
String org=rs5.getString("userid");
  psInsert.setString( 1, pro );
  psInsert.setString( 2, use );
  psInsert.setString( 3, ski );
  psInsert.setString(4,org);
  psInsert.executeUpdate();




 
 




  if( re.next() ) {

System.out.println(re.getString("username"));
System.out.print("Already sent");

//email already sent...



   
  }

else {

....

MY PROBLEM IS IF USER SELECT 2 SKILLS OR MORE, ONCE EMAIL IS SENT FOR THE FIRST SKILL, IT WOULD NOT BE SENT FOR THE SECOND SKILL AND SO ON..
}
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9863985
I'm kind of confused :(
would you explain your logic psudo code. like:
if user set notify on in table ... then
  check the match of project skill and user skill.
  for each matched skill
    if it is already in useremail table which means email is alread sent, then
       do what
    else
       insert ...
    end if
  end for
end if
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9865208
Hi Jasbin.

This is your question:

>Question Title: Help-Query statement !!!
>hi,
> i need help for query.
>How to do a query that ,
>first checks whether notify is yes,
>and if yes, it needs to check whether the info is in the table and if the info not in table >then only send email.

Now this is your answer:

SELECT projects.username AS username, projects.projectid AS projectid, projects.skill AS skill, " " AS userid
FROM projects, userskills, users
WHERE ((projects.username=users.username and users.notify="yes") and (projects.username=userskills.username and projects.skill=userskills.skill)) and projects.username+projects.projectid+projects.skill not in (Select useremail.username+useremail.projectid+useremail.skill from useremail, projects, users where (projects.username + projects.projectid + projects.skill=useremail.username + useremail.projectid + useremail.skill) and (useremail.username = users.username and users.notify = "yes")) and projects.username='" + vol.getUsername()+ "'";

This Query gives you all the projectid and skills that hasn't been sent to the user, if you want to send a mail for each record just loop through it as kenethxu said. if you have a different question, ask for it but this one is answered with this as far as I Know.

Javier
0
 

Author Comment

by:Jasbir21
ID: 9865368
Javier,
  Sorry for confusing things but i guess i am slow learner that i am not getting the answer: this is what i did to try out the string:
<%@ page import="java.sql.*, java.util.*, com.dhal.*"%>

 
<jsp:useBean id="vol" class="com.dhal.volun" scope="session"/>
<jsp:setProperty name="vol" property="*"/>



<Html>

<Head>
<Title> Volunteer DB Form </Title>
</Head>
       

<body bgcolor="#FFF8DC">


<%

//***Declaration of variables...

String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
Connection connection = null;
Statement statement = null;
PreparedStatement prep1=null;

ResultSet rs = null;
String username="";
String passwd="";
String verifypwd="";
String name="";
String edu="";
String city="";
String state="";
String postcode="";
String qualify1="";
String house1="";
String office1="";
String mobile1="";
String email="";
String notify="";
String add1="";
String add2="";
String timestamp="";
String skill="";




try
{

  //**declaration of variables..
 
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   connection = DriverManager.getConnection(connectionURL, "", "");

 statement=connection.createStatement();

   //**querying the database..
String query="SELECT projects.username AS username, projects.projectid AS projectid, projects.skill AS skill,  AS userid
FROM projects, userskills, users
WHERE ((projects.username=users.username and users.notify="yes") and (projects.username=userskills.username and projects.skill=userskills.skill)) and projects.username+projects.projectid+projects.skill not in (Select useremail.username+useremail.projectid+useremail.skill from useremail, projects, users where (projects.username + projects.projectid + projects.skill=useremail.username + useremail.projectid + useremail.skill) and (useremail.username = users.username and users.notify = "yes")) and users.username='yui'";


   rs=statement.executeQuery(query);


  }
catch(Exception ie){
ie.printStackTrace() ;

}

%>
<html>
<body>
<%
if(rs.next()){

while(rs.next()){


out.println(rs.getString("username"));
}
}
%>
</body>
</html>

Note: I took out"", coz i had errors and i still got error. It was my fault that i change username to userid for project becoz, i thought that if projects.username=userskills.username there might be confusion

For example:users(username,notify...)
username   notify  ..
u1             yes

userskills(username,skill)
userskills       skill
u1                Teacher
u1                 Nurse

projects(username,projectid,skill)
userid projectid skill
little      1          Teacher
little       2         Teacher
mercy    1          Teacher
and so on
Me CHANGED THE USERNAME HERE TO USERID

useremail(username,userid,projectid,skill)-lets say email, is sent to user, teh username of user,userid of organization,projectid or organization and skill is stored.
username userid  projecid skill
u1            little         1      Teacher

but the rest is same.
Again , i would applogize for the confusion but i didn't change anything else.




I guess my logic is not ok:

if user set notify=yes on the  table  
  check the match of project skill and user skill.
  for each matched skill
    if it is already in useremail table which means email is alread sent, then
       do nothing ( for debuging purpose i put email already sent)
    else
       insert into the useremail table,
        send email.
    end if
  end for
end if
Thanks
0
 

Author Comment

by:Jasbir21
ID: 9865415
I think i explanin logic, confusing,

I am redoing it :
//See whether the notify is yes..
if(rs1.getString("notify").equals("yes"))
  {
//Select all the skills from userskills that matches with the projects
String que="Select projects.projectid,projects.skill,projects.userid,userskills.username from projects,userskills,users where users.username=userskills.username and projects.skill=userskills.skill and  users.username='"+vol.getUsername()+"'";
stmt3=connection.createStatement();
rs5=stmt3.executeQuery(que);


while(rs5.next()){
//Check whether email is already sent or not, i thought to see whether email is sent or not,each time an email is sent to the user, u store info such as userid of organization,projectid,skill,and so on..


String q="select userskills.username from projects,userskills,users ,useremail where useremail.username=users.username and useremail.username=userskills.username and  projects.projectid=useremail.projectid and projects.userid=useremail.userid and projects.skill=useremail.skill and useremail.skill=userskills.skill and userskills.username='yu'"; //'"+vol.getUsername()+"'";
stmt2=connection.createStatement();
re=stmt2.executeQuery(q);


//If the info is in useremail table,it means email i s already sent
  if( re.next() ) {
//I print this for debug purpose
System.out.print(re.getString("username"));
System.out.print("Already sent");

//email already sent...



   
  }

else {

//else you get the info and put in useremail table and send email
  String pro = rs5.getString( "projectid" ); // can be other types like int
  String use = rs5.getString( "username" );
  String ski = rs5.getString( "skill" );
  String org=rs5.getString("userid");

String sqlInsert="insert into useremail(projectid, username, skill,userid) values( ?,?,?,?)";
 psInsert = connection.prepareStatement( sqlInsert );


  psInsert.setString( 1, pro );
  psInsert.setString( 2, use );
  psInsert.setString( 3, ski );
  psInsert.setString(4,org);
  psInsert.executeUpdate();

 psInsert.close();

...sendt email code..
 
  }//else of re

 }//end of while

} end of if notify

I hope i explain ok,  i need to say ok.

I guess my logic is not ok???
0
 
LVL 6

Expert Comment

by:jarasa
ID: 9865659
Hi Jasbir.

Try this:

<%@ page import="java.sql.*, java.util.*, com.dhal.*"%>

 
<jsp:useBean id="vol" class="com.dhal.volun" scope="session"/>
<jsp:setProperty name="vol" property="*"/>



<Html>

<Head>
<Title> Volunteer DB Form </Title>
</Head>
       

<body bgcolor="#FFF8DC">


<%

//***Declaration of variables...

String connectionURL = "jdbc:mysql://localhost:3306/mydatabase?user=;password=";
Connection connection = null;
Statement statement = null;
PreparedStatement prep1=null;

ResultSet rs = null;
String username="";
String passwd="";
String verifypwd="";
String name="";
String edu="";
String city="";
String state="";
String postcode="";
String qualify1="";
String house1="";
String office1="";
String mobile1="";
String email="";
String notify="";
String add1="";
String add2="";
String timestamp="";
String skill="";




try
{

  //**declaration of variables..
 
   Class.forName("com.mysql.jdbc.Driver").newInstance();
   connection = DriverManager.getConnection(connectionURL, "", "");

 statement=connection.createStatement();
 
   //**querying the database..
String query="SELECT projects.username AS username, projects.projectid AS projectid, projects.skill AS skill, ' ' AS userid FROM projects, userskills, users WHERE ((projects.username=users.username and users.notify='yes') and (projects.username=userskills.username and projects.skill=userskills.skill)) and projects.username+projects.projectid+projects.skill not in (Select useremail.username+useremail.projectid+useremail.skill from useremail, projects, users where (projects.username + projects.projectid + projects.skill=useremail.username + useremail.projectid + useremail.skill) and (useremail.username = users.username and users.notify = 'yes')) and users.username='yui'";

   rs=statement.executeQuery(query);
      if(rs.next()){

            out.println("<BR>" + rs.getString("username") + " " + rs.getString("projectid") + " " + rs.getString("skill") + " " + rs.getString("userid"));
            
            while(rs.next()){
                  out.println("<BR>" + rs.getString("username") + " " + rs.getString("projectid") + " " + rs.getString("skill") + " " + rs.getString("userid"));
            }


      } else {
            out.println("NO RECORDS MATCH QUERY");
      }
}catch(Exception ie){
out.println(ie.getMessage()) ;

}

%>
%>
</body>
</html>

Javier

P.S.: Where are you from?
0
 

Author Comment

by:Jasbir21
ID: 9865740
Javier,
 this is the error i got:
Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Select useremail.username+useremail.projectid+useremail.skill " %>

I guess i am still confusing you, the project username (i think) cannot be the same as the other username, that 's why i changed it.
This is because they represent different people:

For example:users(username,notify...)
username   notify  ..
u1             yes

userskills(username,skill)
userskills       skill
u1                Teacher
u1                 Nurse

projects(username,projectid,skill)
userid projectid skill
little      1          Teacher
little       2         Teacher
mercy    1          Teacher
Username changed to userid

I know i am really confusing things, but i hope now it is ok.I mean if username is there, the username for user would be confused then.
Thanks
0
 
LVL 6

Assisted Solution

by:jarasa
jarasa earned 80 total points
ID: 9865850
Hi Jasbir

I think you should put both in projects and also a sentemail check then you could avoid using useremail table and do it all through projects.

projects(username,userid,projectid,skill,sentemail)

and do this query:

SELECT projects.*
FROM projects, userskills, users
WHERE ((projects.username=users.username and users.notify="yes") and (projects.username=userskills.username and projects.skill=userskills.skill)) and projects.sentemail= "no";

This will be easier so you just have to loop through the resulset, send the email and update the record with sentemail='yes'

Hope this makes sense to you.

Javier
0
 

Author Comment

by:Jasbir21
ID: 9866453
hi,
  it works, it is a combination of yours and Kennethyu.

Thank you so much and i would like to apologize again for inconvinience caused and trouble and for confusing things.

0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9867184
Glad to know you got it straighten finally and thanks for the A grade :-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Spring Tuorial 1 107
password protect pdf 11 72
TreeSet comparator example 7 87
listing all functions in JavaScript 19 101
A safe way to clean winsxs folder from your windows server 2008 R2 editions
What is Backup? Backup software creates one or more copies of the data on your digital devices in case your original data is lost or damaged. Different backup solutions protect different kinds of data and different combinations of devices. For e…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 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