SQL and adding another table

jaypatel posted this at 15:56 — 7th August 2006.

They have: 1 posts

Joined: Aug 2006

Hi,

I am using Crystal Reports / SQL Enterprise Manager.
I have the following SQL:

SELECT tblRegistrationModifier.OrganisationID, tblOrganisation.Organisation, tblRegistrationModifier.RegistrationModifier, tblRegistrationModifier.UsageMax, tblRegistrationModifier.UsageCurrent
FROM tblRegistrationModifier INNER JOIN tblOrganisation ON tblRegistrationModifier.OrganisationID = tblOrganisation.OrganisationID
WHERE tblRegistrationModifier.FrameworkID = 2 AND tblRegistrationModifier.OrganisationID NOT IN (SELECT tblRegistrationModifier.OrganisationID FROM tblRegistrationModifier WHERE tblRegistrationModifier.OrganisationID = 11939 OR tblRegistrationModifier.OrganisationID = 11792 OR tblRegistrationModifier.OrganisationID = 11783 OR tblRegistrationModifier.OrganisationID = 11489 OR tblRegistrationModifier.OrganisationID = 11881 OR tblRegistrationModifier.OrganisationID = 11465 OR tblRegistrationModifier.OrganisationID = 11852 OR tblRegistrationModifier.OrganisationID = 11749 OR tblRegistrationModifier.OrganisationID = 11790).

But now i need to add the following field name: tblRegistrationModifier."Active" where the result equals "True"..... there are only 2 results for this field.

Can anyone help me please.

PS..... This is my first post, so please be gentle.

Kind regards

Jay

Greg K posted this at 19:55 — 7th August 2006.

He has: 2,145 posts

Joined: Nov 2003

Just reformatting your SQL for easier reading by myself and others.

<strong>SELECT</strong> 
   tblRegistrationModifier.OrganisationID,
   tblOrganisation.Organisation,
   tblRegistrationModifier.RegistrationModifier,
   tblRegistrationModifier.UsageMax,
   tblRegistrationModifier.UsageCurrent
<strong>FROM</strong> 
   tblRegistrationModifier
<strong>INNER JOIN</strong> 
   tblOrganisation <strong>ON</strong> tblRegistrationModifier.OrganisationID = tblOrganisation.OrganisationID
<strong>WHERE</strong> 
   tblRegistrationModifier.FrameworkID = 2 <strong>AND</strong> 
   tblRegistrationModifier.OrganisationID <strong>NOT IN </strong>
   (
      <strong>SELECT</strong> 
         tblRegistrationModifier.OrganisationID 
      <strong>FROM </strong>
         tblRegistrationModifier 
      <strong>WHERE</strong>
         tblRegistrationModifier.OrganisationID = 11939 <strong>OR</strong>
         tblRegistrationModifier.OrganisationID = 11792 <strong>OR</strong>
         tblRegistrationModifier.OrganisationID = 11783 <strong>OR</strong>
         tblRegistrationModifier.OrganisationID = 11489 <strong>OR</strong>
         tblRegistrationModifier.OrganisationID = 11881 <strong>OR</strong>
         tblRegistrationModifier.OrganisationID = 11465 <strong>OR</strong>
         tblRegistrationModifier.OrganisationID = 11852 <strong>OR</strong>
         tblRegistrationModifier.OrganisationID = 11749 <strong>OR</strong>
         tblRegistrationModifier.OrganisationID = 11790
   )

'
-Greg