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:(I'm not 100% on what you want. Is this your table structure?

Person
PID Firstname etc
-- ---
1 Joel
2 Bell
3 Bruce
4 Harry

PersonPerson
PID1 PID2
-- --
1 2
4 3

If so - this should help:

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:

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 :) )|||Great ! :)

I will try the code right away..

I'll send you the results i got :)

Mirador.|||Make sure you're using the latest code - I've edited the post a few times since I first posted it (stupid syntax bug)|||Super-great !!!!! :)

It worked like a dream..

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

Owe u a digital-beer.

Mirador.|||lol - no worries mate.

But I've gotta drive tonight - so how about a digital soda?|||/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 (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.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.|||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):

strSQl = "SELECT Person.PID" & _
" FROM Person INNER JOIN (Kjrety INNER JOIN PersonKjrety ON Kjrety.KID = PersonKjrety.KID) ON Person.PID = PersonKjrety.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?|||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.|||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:

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! :D|||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..|||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! :DThese 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