Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: Pick M or O but not both!

    Hi,

    I am still struggling with this query which should pick M or O but not both!


    SELECT First(Centre.ECDL_ID) AS FirstOfECDL_ID, [Title] & " " & [Forename] & " " & [Surname] AS Contact, Centre.centrename, Centre.address1, Centre.address2, Centre.town, Centre.county, Centre.postcode, CentreContact.EMail, CentreContactType.CentreContactType

    FROM ((Centre INNER JOIN CentreContact ON Centre.CentreID = CentreContact.CentreID) INNER JOIN CentreStatus ON Centre.StatusID = CentreStatus.StatusID) INNER JOIN CentreContactType ON CentreContact.CentreContactTypeID = CentreContactType.CentreContactTypeID

    GROUP BY [Title] & " " & [Forename] & " " & [Surname], Centre.centrename, Centre.address1, Centre.address2, Centre.town, Centre.county, Centre.postcode, CentreContact.EMail, CentreContactType.CentreContactType, CentreStatus.Status, Centre.OMR

    HAVING (((CentreContactType.CentreContactType)="M" Or (CentreContactType.CentreContactType)="0") AND ((CentreStatus.Status)="AD") AND ((Centre.OMR)=-1))

    ORDER BY First(Centre.ECDL_ID);


    I tried the UNION query but it still included both Contacts from the same Centre whose CentreContactType’s were M and O, I want to say if M exists show M but if BOTH M and O exist at the same Centre show O instead of M.

    Any help woul be very appreciated.

    Jnr.

  2. #2
    Join Date
    Oct 2003
    Location
    Germany - Stuttgart
    Posts
    14
    Hi,

    did you try the

    select case when
    from...-statement

    select case
    when value = 'M'
    then 'M'
    when value = 'O' or 'M'
    then 'o'
    from...
    where....

  3. #3
    Join Date
    Oct 2003
    Posts
    58
    Thanks, i will try that.....

    its really bugging me!

    Jnr

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •