Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajeshkumar77
Starting Member
46 Posts |
 Posted - 2002-02-18 : 00:35:28
|
| Dear Friends i want to retrive the data from 2 tables like this.table name : studentscolumn : snotable name : empcolumn : enamei used the query like this..select students.sno, emp.ename from students, emp where students.sno=students.sno, emp.ename=emp.enamebut the problem is, it is showing the records 2 times (twice)in students table only one record is there and in emp table only one recrd is in database.but when i use above query it is showing 2 records instaed of grouping into one record.please help me.i am check BOL. but i am not able to find.Thanks in advancelooking forward to hearing from you. |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-18 : 00:47:40
|
| This Query should help you. select ( select sno from students ) as StudentNo, ( select ename from emp ) ENameAm little curious about what you are doing with this kinda query??.--------------------------------------------------------------"Happiness is not something you experience, it's something you remember." |
 |
|
|
rajeshkumar77
Starting Member
46 Posts |
Posted - 2002-02-18 : 01:00:37
|
Dear Nazim,Thank you very much. your reply helping me a lot.However, it is showing an error if i insert 2 records in students table and 2 records in emp table.please check this.Thanks in advance.looking forward to hearing from you again.yoursRajeshServer: Msg 512, Level 16, State 1, Line 3Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.quote:
This Query should help you. select ( select sno from students ) as StudentNo, ( select ename from emp ) ENameAm little curious about what you are doing with this kinda query??.--------------------------------------------------------------"Happiness is not something you experience, it's something you remember."
|
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-18 : 02:13:53
|
| yeah Rajesh , it was meant only for a single record.i fail to understand why are you trying to join two altogether different table which dont hold any relationship between them.Is this a kind of test or what?.you can do a union of them , but the result will not come in the way you want it to.--------------------------------------------------------------"Happiness is not something you experience, it's something you remember." |
|
|
|
rajeshkumar77
Starting Member
46 Posts |
Posted - 2002-02-18 : 04:38:12
|
Dear Nazim,Thank you very much.Now my problem is solved.I made a small mistake in my front-end program. thatswhy i was fully confused with this.Sorry, don't mind.Now it is solved.I used the query like this...select students.sno, emp.ename from students, emp where students.sno like '" + snoTxt.getText() and emp.eno = students.eno;Earlier some problem with my java program.now it is solved.thank you very much.YoursRajeshquote:
yeah Rajesh , it was meant only for a single record.i fail to understand why are you trying to join two altogether different table which dont hold any relationship between them.Is this a kind of test or what?.you can do a union of them , but the result will not come in the way you want it to.--------------------------------------------------------------"Happiness is not something you experience, it's something you remember."
|
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-18 : 10:24:53
|
Rajesh, i would suggest you to select studentno and empname seperately by using two different recordset. instead of following it this way. this is a very inefficent way and will give you huge performace lossquote:
select students.sno, emp.ename from students, emp where students.sno like '" + snoTxt.getText() and emp.eno = students.eno;
HTH--------------------------------------------------------------"Happiness is not something you experience, it's something you remember." |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-18 : 15:40:06
|
| Okay Nazim, now you have me baffled. Since he added the Students.eno and emp.eno fields which are the join fields, what's the problem, other than a LIKE statement is less efficient than an = statement.Also, Rajesh, you might want to change your syntax to put the join in your FROM clause instead of in the WHERE clause. It would look something like this:select students.sno, emp.ename from studentsJOIN emp on students.eno = emp.enowhere students.sno like '" + snoTxt.getText() + "'"And as mentioned above, if the value of snoTxt.getText() is the exact value of students.sno, then use = instead of LIKE.And, as long as we're talking about performance, you should also consider putting this into a Stored Procedure instead of directly in your other code. Pass in snoTxt.getText() as a parameter to the procedure.------------------------GENERAL-ly speaking...Edited by - AjarnMark on 02/18/2002 15:42:20 |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-18 : 23:56:21
|
| oops! Mark you are right , i didnt noticed that Rajesh added another column in his schema mentioned in his original question.Rajesh's first query was thisselect students.sno, emp.ename from students, emp where students.sno=students.sno, emp.ename=emp.enameseeing this i thought therez no common key between students and emp table. Anywayz , thanx for pointing out. will be careful next time.--------------------------------------------------------------"Happiness is not something you experience, it's something you remember."Edited by - Nazim on 02/18/2002 23:59:45 |
|
|
|
rajeshkumar77
Starting Member
46 Posts |
Posted - 2002-02-25 : 05:53:22
|
Dear Mark and Nazim,I am deeply grateful to for your kind co-operation with me.I learned lot of this with your replies.Thanks a lot.Please do continue ur valuable replies to all our newbies.Thanks once again.YoursRajeshquote:
oops! Mark you are right , i didnt noticed that Rajesh added another column in his schema mentioned in his original question.Rajesh's first query was thisselect students.sno, emp.ename from students, emp where students.sno=students.sno, emp.ename=emp.enameseeing this i thought therez no common key between students and emp table. Anywayz , thanx for pointing out. will be careful next time.--------------------------------------------------------------"Happiness is not something you experience, it's something you remember."Edited by - Nazim on 02/18/2002 23:59:45
|
|
|
|
|
|
|
|
|
sql select query