Outer Right Join on SubQuery

[iheartvba]

Hi Everyone,

I am new to SQL Server, but have played around with MS Access a fair bit.

I am currently trying to create an Outer Right Join on a Table and Query in SQL Server. Heres query from Access Query builder below:

SELECT tblPhone.custID, NotMobile.CustMoreThanMob
FROM NotMobile RIGHT JOIN tblPhone ON NotMobile.CustMoreThanMob = tblPhone.c_uid
WHERE (((NotMobile.CustMoreThanMob) Is Null));

Notes:
1. tblPhone is a Table and it contains the field CustID
2. NotMobile is a Query and it contains the field CustMoreThanMob
2.Heres the query that defines "NotMobile" in the above query:

SELECT Left([PhoneNumber],3) AS Type, tblPhone.custID AS CustMoreThanMob 
FROM tblPhone
GROUP BY Left([PhoneNumber],3), tblPhone.custID
HAVING (((Left([PhoneNumber],3))<>614));

I have tried to get the top query to run in SQL Server in the following ways:
1.

SELECT custID, CustMoreThanMob
FROM NotMobile OUTER RIGHT JOIN tblPhone ON NotMobile.CustMoreThanMob = tblPhone.c_uid
WHERE CustMoreThanMob Is Null;

* Please note that all instances of the query "NotMobile" have been replaced by the words "NotMobile". In the actaul query I have put the whole SQL String in round brackets.


Thank You in Advance

[gpl]

I dont have SQL Server with me, so I cannot verify the code

Basically, your sub query needs to be set up as a derived table so that it can be joined.

Many years ago I read an article that said SQL Server is optimised for Left (as opposed to Right) outer joins, so have re-written it that way.

This should get you started

SELECT tblPhone.custID, NotMobile.CustMoreThanMob
FROM  tblPhone    left outer join
(
  SELECT Left([PhoneNumber],3) AS Type, tblPhone.custID AS CustMoreThanMob
  FROM tblPhone
  GROUP BY Left([PhoneNumber],3), tblPhone.custID
  HAVING (((Left([PhoneNumber],3))<>614)) 
) NotMobile
 
ON NotMobile.CustMoreThanMob = tblPhone.c_uid
WHERE (((NotMobile.CustMoreThanMob) Is Null))

[NeoPa]

You don't seem to say what your actual problem is, but in case it helps, the RIGHT OUTER JOIN is a type of outer join qualified by RIGHT. Not a type of right join qualified by OUTER.

MSSQL can be very forgiving, so I can't say what you've used will necessarily fail (and I have no info from you on the matter) but I wouldn't expect that to work.

In Jet SQL (used by Access) that you're more used to there is only support for the LEFT & RIGHT OUTER JOINs (just called LEFT JOIN & RIGHT JOIN generally) but MSSQL also supports the third type (FULL) of OUTER JOINs.

SQL JOINs may be of some help.