Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Unanswered: How do i "Join" back to the same table and fields ? Please help !

    Hi and thanx for reading my post..

    Underneat is the current SQL i'm trying to get to work (in Access 2000)

    Cant get it to work, cause i'm getting Join errors..

    -----------CODE--------------
    strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname" & _
    " FROM Person p, Person p1" & _
    " INNER JOIN (PersonPerson pp ON pp.PID = p.PID OR pp.PID2 = p.PID)" & _
    " WHERE (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
    " OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
    ----------END CODE-----------

    -------------DESCRIPTION--------------------
    1) Got a table "Person" where i'm inserting PID and Lastname.
    2) Registering person 1-> PID = 1, Lastname = Doe
    3) Registering person 2-> PID = 2, Lastname = John
    4) Using table PersonPerson (manually) to connect these two together.
    PID1 = 1, PID2 = 2
    5) By using this i can use SQL to get the data from both persons taht are
    connected
    6) So basically, i want to go through all records in the personperson table, to find PID and PID2, and then use _both_ the fields to check against the Person-table. First getting Person.PID and Person.Lastname (For PERSON 1) THEN... do the exact same operation for PID2.


    -------------END DESCRIPTION------------------

    Result should look like this :

    Person1 ID, Person1 Lastname, Person2 ID, Person2 Lastname
    ----------------------------------------------------------
    1 Doe 2 John



    Will be enormously happy if anyone could help me out with this code...

    /(_Mirador
    Best regards
    Mirador

  2. #2
    Join Date
    Jul 2003
    Posts
    73
    I'm not 100% on what you want. Is this your table structure?
    Code:
    Person
    PID  Firstname  etc
    ---  ---------
    1    Joel
    2    Bell
    3    Bruce
    4    Harry
    
    PersonPerson
    PID1  PID2
    ----  ----
    1     2
    4     3
    If so - this should help:
    Code:
    strSQl = "SELECT p.PID, p.Lastname, p1.PID, p1.Lastname
    FROM Person p, Person p1, PersonPerson pp
    WHERE (p.PID = pp.PID1 AND p1.PID = pp.PID2)
    AND (pp.PID LIKE '*" & CStr(Me.txtSearch) & "*'" & _
    " OR pp.PID2 LIKE '*" & CStr(Me.txtSearch) & "*')"
    It will generate the following output:

    Code:
    PID         Lastname   PID         Lastname   
    ----------- ---------- ----------- ---------- 
    1           Dixon      2           Crawford
    4           Potter     3           Shark
    (Yes, Person 4's name is Harry Potter - but no - I haven't read the books )
    Last edited by joeldixon66; 05-14-04 at 05:32. Reason: spaces don't come fixed width with Courier
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  3. #3
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45
    Great !

    I will try the code right away..

    I'll send you the results i got

    Mirador.
    Best regards
    Mirador

  4. #4
    Join Date
    Jul 2003
    Posts
    73
    Make sure you're using the latest code - I've edited the post a few times since I first posted it (stupid syntax bug)
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  5. #5
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Talking

    Super-great !!!!!

    It worked like a dream..

    You have no clue how much you've helped me !

    Owe u a digital-beer.

    Mirador.
    Best regards
    Mirador

  6. #6
    Join Date
    Jul 2003
    Posts
    73
    lol - no worries mate.

    But I've gotta drive tonight - so how about a digital soda?
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  7. #7
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Question

    /Me hands over a digital master-soda

    hehe..

    btw : since you're so master'ish at SQL.. maybe u could try and help me with this query too ?

    -----------CODE--------------
    strSQl = "SELECT Person.PID" & _
    " FROM Person INNER JOIN (Kjøretøy INNER JOIN PersonKjøretøy ON Kjøretøy.KID = PersonKjøretøy.KID) ON Person.PID = PersonKjøretøy.PID" & _

    " WHERE Person.Etternavn LIKE '%" & Me.txtSearch & "%' OR Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
    " OR Person.Alias LIKE '%" & Me.txtSearch & "%' OR Person.Alias LIKE '%" & Me.txtSearch2 & "%' OR Person.Yrke LIKE '%" & Me.txtSearch & "%' OR Person.Yrke LIKE '%" & Me.txtSearch2 & "%'" & _ etc.etc.etc...
    ---------- END CODE ---------------

    ----------- DESCRIPTION--------------------
    As you can see it's 2 x fields where i want to search..
    Basically... i'm running 2 different queries depending on (and checking) if only one, or two fields are filled in for the search... AND...

    If both the fields are filled in, it should ex : if Me.txtsearch is "June" and Me.txtSearch2 is "2004" make sure it's only getting a record if both "June" and "2004" is included in _ANY_ of the fields in the record..

    I have realized that i cannot only use AND alone, because then all the fields have to match, and i cannot use OR alone either.. because then it grabs if it only matches one of them..

    Got any clues on this one ?

    Thanx for your help mate..

    Mirador.
    Best regards
    Mirador

  8. #8
    Join Date
    Jul 2003
    Posts
    73
    So basically - you are trying to select records in which BOTH of the criteria is in ONE of the fields. If this is correct (and if it's not I'm lost ) - then this should help (take note of the brackets):

    Code:
    strSQl = "SELECT Person.PID" & _
    " FROM Person INNER JOIN (Kjøretøy INNER JOIN PersonKjøretøy ON Kjøretøy.KID = PersonKjøretøy.KID) ON Person.PID = PersonKjøretøy.PID" & _
    
    " WHERE (Person.Etternavn LIKE '%" & Me.txtSearch & "%' AND Person.Etternavn LIKE '%" & Me.txtSearch2 & "%'" & _
    ") OR (Person.Alias LIKE '%" & Me.txtSearch & "%' AND Person.Alias LIKE '%" & Me.txtSearch2 & "%') OR Person.Yrke LIKE '%" & Me.txtSearch & "%' AND Person.Yrke LIKE '%" & Me.txtSearch2 & "%')" & _ etc.etc.etc...
    Basically you are saying (i'll use a generic example of a Product record. No offence - but I really can't understand the table you have with the column names ):

    If txtSearch1 is "big" and txtSearch2 is "round"

    SELECT blah
    FROM Product
    WHERE (Product.Name LIKE '%big%' AND Product.Name LIKE '%round%')
    OR (Product.Description LIKE '%big%' AND Product.Description LIKE '%round%')
    OR (Product.Comment LIKE '%big%' AND Product.Comment LIKE '%round%')

    So you will get products with big AND round in either the Name, Description or Comment column.

    Is this what you were after - or have I just spoken pure gibberish?
    Last edited by joeldixon66; 05-14-04 at 15:16.
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  9. #9
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45
    Hay mate thanx for your reply

    did u enjoy your digital soda ? hehe..

    Well... gotta admit that it's a bit confusion for myself too

    I'll try to explain a bit better :

    ----------------------------
    Q :
    Lets say u want to search for every record that includes both "Green" and "Yellow" in _any_ of the fields in the record, and u want to see _only_ those records.

    A:
    You find a record where field "lastname" has "Green" in it and field "Comment" has "Yellow" in it -> MATCH!!
    -------------------------------

    If i understood it right, the one you posted has to have both "Green" and "Yellow" in one field right ?
    So.. u would get a match if ex. "Comment" field had the text :
    "All green people are infact Yellow because they bla-bla.-bla..."

    I have to lets say.. use the second searchfield (txtsearch2) to... "narrow" down the search.

    Example :
    --------------------
    Lets say u want to find a person named "Mike" and u press search. U would maybe get something like 1000 matches if u got a huuge database.
    But then u altso know that "Mike" is from "Uganda". So... therefore i type "Mike" in txtsearch and "Uganda" in txtsearch2 to make sure that u get all the "Mike" records which got "Uganda" in any of the other fields..
    --------------------

    Yea.. that's about it dunno if it's even possible but i surely hope so : )heeh..

    Hope that made things a littlebit more clear

    Thanx for your help btw !!!.. most appreciated..

    want another digital soda ? or.. maybe a digital beer this time

    Best regards
    Mirador.
    Best regards
    Mirador

  10. #10
    Join Date
    Jul 2003
    Posts
    73
    Ahh - I see what you mean. It's also possible - but the code is quite long, depending on the columns in your table. It's basically the same as my other example above - but switch the ANDs and ORs.

    For each search criteria you have to check if it's in any of the columns. Again - using my Product table:

    Code:
    SELECT blah
    FROM Product
    WHERE (Product.Name LIKE <SearchCriteria1> OR Product.Description LIKE <SearchCriteria1> OR Product.Comment LIKE <SearchCriteria1>)
    AND (Product.Name LIKE <SearchCriteria2> OR Product.Description LIKE <SearchCriteria2> OR Product.Comment LIKE <SearchCriteria2>)
    Doing this for each column should get what you're after. Basically you're saying SearchCriteria1 needs to be in any of the columns (using the OR) - AND SearchCriteria2 needs to be in any of the columns.

    Is that what you're after?

    And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch!
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  11. #11
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45

    Talking

    Tjohooo !!..

    yea.. that's JUST what i was after..

    I had thoughts in this track, but wasn't certain because it would be so extremely long hehe.. Didn't know quite how to write it either..

    but.. THANK YOU AGAIN !!

    Double scotch coming up.. or.. maybe it's back to coffee ? heheh !!:



    Mirador..
    Best regards
    Mirador

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by joeldixon66
    And I'm well past a digital beer - with the last few weeks I've had at work. Better make it a digital (double) scotch!
    These last few weeks (since mid-April) have been awful for me too. Do you suppose that the universe has taken some kind of unusually perverse twist against us "denizens of databases" lately?

    -PatP

Posting Permissions

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