right outer join not working.. help required

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

right outer join not working.. help required

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.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

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 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

dracstorey
Starting Member

2 Posts

Posted - 2013-05-07 : 03:47:48

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

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 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources