Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 right outer join not working.. help required		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dracstorey
Starting Member

2 Posts
Posted - 05/06/2013 :  16:10:23  Show Profile  Reply with Quote
Dear all,

I have the following sql query:

select T_cands.cand_id, T_Options.ox
from T_Options right outer join
T_cands on T_cands.cand_id = T_Options.candid

T_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

India
52326 Posts
Posted - 05/07/2013 :  00:19:30  Show Profile  Reply with Quote
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 records

My guess is in SSMS you're running query in different database or in different schema table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dracstorey
Starting Member

2 Posts
Posted - 05/07/2013 :  03:47:48  Show Profile  Reply with Quote
Hi

Thanks 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,1

If 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
Edited by - dracstorey on 05/07/2013 03:48:28
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts
Posted - 05/07/2013 :  03:54:40  Show Profile  Reply with Quote
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 records

You 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000