1 Reply Latest reply on Apr 23, 2007 6:13 AM by Dan Bracuk

    LEFT  JOIN help

    stuart1231 Level 1
      Hi there,

      Please I know there must be a simple answer, I want to allow members to join committees, so ONE user can have have MULTIPLE committees.

      i have set it to look through the committees and put a checkbox beside the ones that a member has selected. It works fine, BUT it is showing all members, rather an a specific one. I have tried WHERE memberID = 'member123" but that only shows the committees that they have already selected.

      So i want to loop through the committees and only show where ONE member is currently selected.

      Here is my SQL

      SELECT
      contactidentifier.identifierID,
      contactidentifier.identifierText,
      contactidentifier_list.contactIdentifier,
      contactidentifier_list.identifierListID

      FROM
      contactidentifier

      LEFT JOIN
      contactidentifier_list
      ON
      contactidentifier.identifierID = contactidentifier_list.contactIdentifier;

      Any help would be most grateful
        • 1. Re: LEFT  JOIN help
          Dan Bracuk Level 5
          you can add a subquery to the end of your current query. without knowing your table structures, I can't be sure about getting it right, but it would be something like

          and contactidentifier_list.contactIdentifier in
          (select contactIdentifier, count(contactIdentifier)
          from contactIdentifier_list
          group by contactIdentifier
          having count(contactIdentifier) = 1)