Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2003
    Posts
    34

    Unanswered: Another tricky SQL problem

    OK I have another tricky SQL query:

    I have two tables:

    Person:
    PK -> MemberID
    FirstName
    LastName
    FK -> ContactID

    Contact:
    PK -> ContactID
    HouseNo
    StreetName
    TownCity


    I want to be able to do a mail merge but only print out one label per address in the Contact table, but sometimes that one address may belong to more than one person, so if that is the case then I want to concatenate those two names on the one label. e.g.

    Jim Smith
    70 Smith Lane
    Boville

    Harry & Mary Booth
    45 Dean Ave
    Hamilton

    Where Harry and Mary Booth are two separate records in the Person table with the same ContactID

    Any idea how I could do this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you'll probably be better off doing this in some application scripting language than trying a "tricky" sql query

    the best a query can do is sort the people by contact

    if you were using MySQL, you could use a GROUP BY and the proprietary GROUP_CONCAT() function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Posts
    34
    Ummm...

    Access supports GROUP BY and concatenation

    I got something like this, but couldnt refine it

    SELECT P1.FirstName + " & " + P2.FirstName, C.HouseNo, C.StreetName, C.TownCity FROM Person P1, Person P2, Contact C

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    GROUP_CONCAT is not just concatenation -- it is the concatetnation of values from a column

    anyhow, your approach will work, but you'd need a left outer join (to handle conditions where there's only one person per contact), and then you'd probaby want to go to three copies of the person table in case there are three people at the same address, or maybe four ...

    ... and it would get very inefficient very quickly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Posts
    34
    Thanks. The system works on the assumption that there would only ever be a maximum of two people at the same address.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, try this --
    PHP Code:
    select P1.FirstName 
         
    iif(isnull(P2.FirstName), ''
             
    ' & ' P2.FirstName)
         , 
    C.HouseNo
         
    C.StreetName
         
    C.TownCity 
      from Contact C
    inner 
      join Person P1
        on C
    .ContactID P1.ContactID
    left outer
      join Person P2
        on C
    .ContactID P2.ContactID
       
    and P1.MemberID <> P2.MemberID 
    it starts with an inner join from contact to person

    i.e. assumes each contact has at least one person

    then it uses LEFT OUTER to try to match a second person for that contact, which must be different from the first person for that contact

    in the SELECT list, IIF is used to see whether the 2nd person was matched (2nd name will be null if there is no 2nd person)

    if there's a 2nd person, it concatenates the ampersand and the 2nd person's name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2003
    Posts
    34
    OK, I admit I have no idea how to do it. Can you give me a hand please.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    give you a hand? i'm sorry, i don't understand

    did you run that query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2003
    Posts
    34
    Sorry I posted that before I realised that you had just given me a hand, my mistake.

    That looks very impressive, thankyou, just one small problem, and I think its a MS Access problem. It gives me a 'Join expression not supported error'

    I think the problem is with the:

    AND P1.MemberID <> P2.MemberID

    part, as if i remove this it works, but not with the required result obviously, any ideas how to make this Access friendly?

    SELECT P1.FirstName + IIF(ISNULL(P2.FirstName), '', ' & ' + P2.FirstName), C.FlatHouseNo, C.StreetName, C.TownCity FROM (Contact C INNER JOIN Person P1 ON (C.ContactID = P1.ContactID)) LEFT OUTER JOIN Person P2 ON (C.ContactID = P2.ContactID AND P1.MemberID <> P2.MemberID)

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you sure your last version doesn't work?

    i notice you put in the join parentheses which i forgot

    good one

    but your last version is exactly the syntax to avoid the "join expression not supported" message

    it is supported with parentheses
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2003
    Posts
    34
    yeah i am sure, i have put parentheses everywhere, but same error, but I dont think that alone is the problem because i took out the first join changed a few thing to make it work, then the second join did work as it is,

    so its a combination of the two joins that is the problem...

Posting Permissions

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