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

    Unanswered: JOIN in Access problem

    Anyone know how to get this quesry to work in MS Access?

    It tells me this Join expression is not supported

    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 JOIN Person P2 ON (C.ContactID = P2.ContactID AND P1.MemberID <> P2.MemberID)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I suspect that what you want is:
    PHP Code:
    SELECT P1.FirstName IIF(ISNULL(P2.FirstName), ''' & ' P2.FirstName)
    C.FlatHouseNoC.StreetNameC.TownCity
       FROM Contact C
       INNER JOIN Person P1 ON 
    (C.ContactID P1.ContactID)
       
    LEFT JOIN Person P2 ON (C.ContactID P2.ContactID)
       
    WHERE P1.MemberID <> P2.MemberID
    ...but that's just a guess.

    -PatP

  3. #3
    Join Date
    Sep 2003
    Posts
    34
    Yeah that will work but i dont think that it will give me the results i want.

    I have a Contact table with address etc, and a Person table with name etc.

    Some people in the person table might share the same address, but only a maxmum of two people may share the same address.

    I want to return the same number of results as there are rows in the Contact table, but if that address belongs to more than one person then i want to concatenate the two firstnames as such

    Paul & Mary Smith
    75 Hall Street
    Blahhh

    otherwise just return the one name

    Bob Jones
    Whatever Road
    Jimville

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How do you tell if two people share an address? In other words, there isn't any referential way for me to know. Would "175 Main St" and "175 Main Street" and "175 Main" be one address, two, or three by your reconning?

    Once you determine that, it should be relatively simple. You can build a relation to the manditory Person using an INNER JOIN, a relationship from one Contact to another optional Contact at the same address using a LEFT JOIN, and a relation to the optional Person for the optional Contact using another LEFT JOIN. Wrap the name for the optional Person inside an Iif() and you should be in business.

    -PatP

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by Pat Phelan
    I suspect that what you want is:
    PHP Code:
    SELECT P1.FirstName IIF(ISNULL(P2.FirstName), ''' & ' P2.FirstName)
    C.FlatHouseNoC.StreetNameC.TownCity
       FROM Contact C
       INNER JOIN Person P1 ON 
    (C.ContactID P1.ContactID)
       
    LEFT JOIN Person P2 ON (C.ContactID P2.ContactID)
       
    WHERE P1.MemberID <> P2.MemberID
    whoa, pat, ain't it amazing how we both arrived at the same solution?

    see http://www.dbforums.com/showthread.php?threadid=990841
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2003
    Posts
    34
    He arrived at that solution r397 because I gave him your solution to start with.

    Anways, i have determined how the system determines shared addresses that is not the problem, the problem, that you call simple, is that noone seems to know how to make this work in Access.

  7. #7
    Join Date
    Sep 2003
    Posts
    34
    Person table:
    PK -> MemberID
    FK -> ContactID

    Contact table
    PK -> ContactID

    This is how they are linked

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    RaydenL appears to be correct. I do this all of the time using Access with SQL Server or MSDE, but due to the limitation of Jet only supporting equi-joins, it appears that this can't be done in one pass using just Access/Jet.

    The answer is to go to two separate queries (one for two people, one query for one person) something like:
    PHP Code:
    SELECT IIf(IsNull(b.PersonID),"",b.firstName " & ") & a.firstName " " a.lastName AS Name 
    Contact.StreetContact.Street2 
    Contact.City ", " Contact.State AS CityState 
    Contact.PostCode 
    FROM 
    (Contact INNER JOIN Person AS a ON Contact.ContactID a.ContactId
    LEFT JOIN Person AS b ON Contact.ContactID b.ContactId
    WHERE  
    (b.personID a.personID)
    UNION SELECT a.firstName " " a.lastName AS Name 
    Contact.StreetContact.Street2 
    Contact.City ", " Contact.State AS CityState 
    Contact.PostCode 
    FROM 
    (Contact INNER JOIN Person AS a ON Contact.ContactID a.ContactId)
    WHERE 1 = (SELECT Count(*) FROM Person AS c
       WHERE  c
    .contactID Contact.contactID); 
    This is rather ugly, but it is the only workaround I can think of for this limitation of Jet. If you switched to MSDE or SQL Server, this would become a non-issue.

    -PatP

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    jet only supports equi-joins? i don't think so, where did you hear that? i'm sure i've done theta joins in access 97...

    anyhow, about raydenl's problem, yes, it is tricky, and yes, i did encounter the "join expression not supported"

    however, i was also able to re-jig the query and i did get it working in access 97 --
    PHP Code:
    create table Contact
    ContactID int not null constraint pk_contact primary key
    HouseNo varchar(10)
    StreetName varchar(10)
    TownCity varchar(10)
    );

    create table Person
    MemberID int not null constraint pk_person primary key
    FirstName varchar(10)
    LastName varchar(10)
    ContactID int not null 
    constraint fk_person foreign key (ContactID
          
    references Contact ContactID )
    );

    insert into Contact 
    values 
    21'123','Sesame St','Anytown');

    insert into Contact 
    values 
    22'70','Smith Lane','Boville');

    insert into Contact 
    values 
    23'45','Dean Ave','Hamilton');


    insert into Person
    values 
    101 'Curly','Howard'21);

    insert into Person
    values 
    102 'Harry','Booth'23);

    insert into Person
    values 
    103 'Mary','Booth'23);

    insert into Person
    values 
    104 'Jim','Smith'22);

      
    select P1.FirstName 
         
    iif(isnull(P2.FirstName), ''
             
    ' & ' P2.FirstName)
         , 
    C.HouseNo
         
    C.StreetName
         
    C.TownCity 
      from Contact C
    inner
      join 

           
    Person P1
    left outer
      join Person P2
        on P1
    .ContactID P2.ContactID  
       
    and P1.MemberID <> P2.MemberID  
           
    )
        
    on C.ContactID P1.ContactID
     where P2
    .MemberID is null
        
    or P1.MemberID P2.MemberID

    Resident
    (s)    HouseNo    StreetName   TownCity
    Curly          123      Sesame St    Anytown
    Harry 
    Mary   45       Dean Ave     Hamilton
    Jim            70       Smith Lane   Boville 
    i'm not happy with the fact that my original query did not work, but somewhat pleased by the fact that i did get it running

    note that the second condition in the WHERE clause picks one of the two rows returned by the self-join of the Person table (Harry & Mary, and Mary & Harry)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Sep 2003
    Posts
    34
    Wow, thankyou guys, most appreciated. It works beautifully. Just one more thing thou. I have been trying to add one more JOIN to it, but I'm not sure where to put it, or how to write it, I keep getting "JOIN not supported errors" again. Access really sucks, but... I have to use it!

    Any chance you could help once more?

    I am trying to add another table called Trans, this table holds expiry date information for members. It can hold many expiry dates for a single member but I only want the MAX one for any one member.

    Trans:
    PK -> TransactionID
    FK -> MemberID
    ExpiryDate

    So, since we have now joined members on the Contact table if there are two people living at the same address, I would now only like to select the members/addresses who also have not yet expired (e.g. MAX(ExpiryDate) >= Date()). But if two members are linked by the one address, and only one of them has expired, then it would be ok to select both members, since otherwise I think it would be too hard.

    Is this too awkward? I can't get my head around it.

    Cheers, Rayden

Posting Permissions

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