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 |
dracstorey
Starting Member
2 Posts |
Posted - 2013-05-06 : 16:10:23
|
Dear all,I have the following sql query:select T_cands.cand_id, T_Options.oxfrom T_Options right outer join T_cands on T_cands.cand_id = T_Options.candidT_cands has 21 records and T_Options has 14.I believe it should return 21 records which is does in sql Server Management Studio but in my asp code (using adodb recordset it only returns 14.Any ideas??Andrew |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-07 : 00:19:30
|
nope. it will not return 21. It will return all records from T_cands ( 14) and for ones having matching values it will retrieve those from the other table T_Options. so unless there's any one to many relationship output will only contain 14 recordsMy guess is in SSMS you're running query in different database or in different schema table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
dracstorey
Starting Member
2 Posts |
Posted - 2013-05-07 : 03:47:48
|
HiThanks for the reply. In fact I had a brainwave last night and found a solution:sql_candidates = "SELECT T_cands.cand_id, T_Options.ox FROM T_Options RIGHT OUTER JOIN T_cands on T_cands.cand_id = T_Options.candid"Set rst_candidates = Server.CreateObject("ADODB.Recordset")rst_candidates.Open sql_candidates, db,1,3,1If you get the recordset this way using the Open Method you only get 14 records. However:sql_candidates = "SELECT T_cands.cand_id, T_Options.ox FROM T_Options RIGHT OUTER JOIN T_cands on T_cands.cand_id = T_Options.candid"Set rst_candidates=db.Execute(sql_candidates)produces the 21 records required.I haven't yet discovered why the Execute Method works and not the Open but it does!Andrew |
 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-07 : 03:54:40
|
I dont know much on application side so cant suggest more reg. Open and Execute. But if you execute query at db end it should always retrieve only 14 recordsYou could try making a procedure with above query and create it in your db. Then try calling the SP using open and execute and see if they work fine------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
|
|