Hello, I have a query that I wa

[bennynye]

Hello,

I have a query that I want to return a list of contacts that do not have any rows with a 't' in the Licenced column in a one-many table. I have checked that this case does occur, but my SQL returns 0 contacts. If anyone could point me in the right direction I would appreciate it.

SELECT     COUNT(Distinct sysdba.CONTACT.CONTACTID) AS Expr1
FROM         sysdba.CONTACT LEFT OUTER JOIN
                      sysdba.C_LICENSE ON sysdba.CONTACT.CONTACTID = sysdba.C_LICENSE.CONTACTID
WHERE     (sysdba.C_LICENSE.CONTACTID NOT IN
                          (SELECT     sysdba.C_License.contactid
                            FROM          sysdba.C_License
                            WHERE      sysdba.C_LICENSE.LICENSED = 'T'))

Thanks,

 

[ThomVF]

SELECT COUNT(Distinct sysdba.CONTACT.CONTACTID) AS Expr1
FROM sysdba.CONTACT
WHERE NOT EXISTS
(SELECT sysdba.C_License.contactid
FROM sysdba.C_License
WHERE sysdba.C_LICENSE.CONTACTID = sysdba.CONTACT.CONTACTID
AND sysdba.C_LICENSE.LICENSED = 'T')


"Helping others to help themselves..."
=================================
Thomas V. Flaherty Jr.
Birch Hill Technology Group, Inc.

http://www.birchhillgroup.com

 

[bennynye]

Great solution ThomVF,

I used your method of going to the Parent table without a join and it worked great. I changed it slightly to incorporate a function that I am more familiar with, the NOT IN, but that was pretty much the only change.

Thanks again,
bennynye