Results 1 to 3 of 3

Thread: Query Help?

  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: Query Help?

    Hello,

    I am wondering if someone could help please - I have a query I'm trying to build and am out of my depth....

    I have a member database that has a single "Contacts" table that stores a list of all members and all contacts associated with those members. The translation to which member is related to which contact is handled by a "Links" table. There are also other sub member accounts which are also stored in "Contacts" that are related to the main member record and has its own contact records all explained via the "Links" table.

    Horrible design IMO, but its not mine!

    What I am trying to do is to create a list of members where a certain link type is NOT present.

    ContactID - Position - LinkFrom - LinkTo - - LinkType
    00000003 - A005/0000 - 00010159 - 00000003 - Head Office
    00000003 - A005/0000 - 00010159 - 00012960 - GVS Primary Contact
    00000008 - A014/0000 - 00010161 - 00000008 - Head Office
    00000008 - A014/0000 - 00010161 - 00003294 - GVS Primary Contact
    00000013 - A021/0000 - 00010163 - 00000013 - Head Office
    00000017 - A027/0000 - 00010164 - 00000017 - Head Office
    00000017 - A027/0000 - 00010164 - 00004032 - GVS Primary Contact
    00000019 - A031/0000 - 00010166 - 00000019 - Head Office
    00000025 - A040/0000 - 00010167 - 00000025 - Head Office
    00000025 - A040/0000 - 00010167 - 00004239 - GVS Primary Contact

    So my result above (hopefully its readable) I have a record for each LinkType, e.g A005/0000 has two, one "Head Office", one "GVS Primary Contact". A021/0000 has only one "Head Office".

    I want to not show a member AT ALL, IF they have a "GVS Primary Contact" record in the Links table.

    My ideal result for the above would be:

    ContactID - Position - LinkFrom - LinkTo - - LinkType
    00000013 - A021/0000 - 00010163 - 00000013 - Head Office
    00000019 - A031/0000 - 00010166 - 00000019 - Head Office

    I've tried a few things but cant get it to exclude the member completely where a GVS Primary Contact exists in the Links table. If I say <> 'GVS Primary Contact' I get the members I want to exclude, it just escludes those records from the result - I still see them with Head Office Records.

    I hope this makes sense and someone is kind enough to help?

    Here is my SQL:

    Code:
    select top 10 m.ContactID, m.Position, lgvs.LinkFrom, lgvs.LinkTo, lgvs.LinkType
    from Contacts m inner join
    UserDefFields udf on m.ContactID = udf.ContactID inner join
    SubscriptionMembers sm on m.ContactID = sm.ContactID inner join
    Links lm on m.ContactID = lm.LinkFrom and lm.LinkType = 'GVS Member' inner join
    Contacts gvsm on lm.LinkTo = gvsm.ContactID inner join
    Links lgvs on gvsm.ContactID = lgvs.LinkFrom
    where sm.[Current] = 1 and
    sm.MainSubscription = 1 and
    sm.Lapsed = 0 and
    m.CategoryCode in ('01','02','03','04','05','06','07','08','09','10','11','12') and
    (udf.Check17 = 1 or udf.Check8 = 1) 
    order by m.Position

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    with 
        CTE_GVS_Primary as
        (
            select LinkFrom from Links
            where LinkType = 'GVS Primary Contact'
        ),
    
        CTE_Links as
        (
            select l.LinkFrom, l.LinkTo, l.LinkType 
            from Links as l
            left join CTE_GVS_Primary as g on g.LinkFrom = l.LinkFrom
            where g.LinkFrom is null
        )
    
    select top 10 m.ContactID, m.Position, lgvs.LinkFrom, lgvs.LinkTo, lgvs.LinkType
    from Contacts m 
    inner join UserDefFields udf on m.ContactID = udf.ContactID 
    inner join SubscriptionMembers sm on m.ContactID = sm.ContactID 
    inner join Links lm on m.ContactID = lm.LinkFrom and lm.LinkType = 'GVS Member' 
    inner join Contacts gvsm on lm.LinkTo = gvsm.ContactID 
    inner join CTE_Links lgvs on gvsm.ContactID = lgvs.LinkFrom
    where sm.[Current] = 1 and
          sm.MainSubscription = 1 and
          sm.Lapsed = 0 and
          m.CategoryCode in ('01','02','03','04','05','06',
                             '07','08','09','10','11','12') and
          (udf.Check17 = 1 or udf.Check8 = 1) 
    order by m.Position
    Hope this helps.

  3. #3
    Join Date
    May 2012
    Posts
    3
    Hi,

    Brilliant! Thank-you very much, that's worked perfectly.

    Not sure whats its doing yet, but I'll try to pick it apart - looks like I could make use of that on other things too.

    You've made my day.

    R

Posting Permissions

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