Hi again folks! I tried to my best to browse through the forums for an answer to my problem, but to my confusion the things I encountered there and tried didn't help me a lot so I humblely ask for your help on this:

My starting page has this RS:
SELECT *
FROM lost, objectCategory, Districts
WHERE lost.active = TRUE AND lost.catID = objectCategory.CatID AND lost.municipalityNr LIKE Districts.municipalityNr
ORDER BY lostDate DESC

Explaination for RS:
I need to pull from these 3 tbls to present "category names" and "municipality names" on my page instead of the numbers the table "lost" presents. so far so good....

the problem starts when I'm filtering the resultpage:
I use this variable from the searchfield form ---> varSW 1 Request.QueryString("swLost"

SELECT DISTINCT lostID, lostDate, objectDescription, District.municipalityName
FROM lost, objectCategory, District
WHERE (objectDescription LIKE '%varSW%') OR ((lost.municipalityNr LIKE District.municipalityNr) LIKE '%varSW%')

I tried not to DISTINNCT, just SELECT * to start with and that produced the same result so now I'm not even sure if to use this metode.
Using the above mentioned returns a strange number of records in the sense that Norway has 436 municipalities and not 429 as the result of my query (before I integrated the objectCategory..) so it's obviously not a duplicate produced for every municipalityNr??!
So what I need is a RS that preforms searches(based upon the input from "varSW" in the table "lost" field catID (nr) but return the name from objectCategory.objectType (relations in tables ok) OR in the table "lost" field objectCategory OR table "lost" field municipalityNr but return the name from District.municipalityName (also relations ok)

confused? well I'll be glad to fill in more but for you gurus I dont expect this should be a problem.
Thanks in advance from Norway

Edited by - owu on 09 Feb 2005 19:19:23

Can you explain / lay out your table definitions. I sense something might be wrong with the joins - but will need to understand the relationships to work out what's going on here.

Also what database are you using?

Live the life you love
Love the life you live
~ ~ ~ ~ ~ ~ ~
<b>Simon Martin</b> - <i>DMXzone Manager</i>
<font size=1>[ Dreamweaver MX/MX2004 | ASP | SQL | XHTML/CSS | Web Accessibility ] </font id=size1>

Hi Simon
In the example above I translated fields to eng. in order for it to make more sense.
Well explaining the table layout I can but then you'll get the hard facts with some Norwegian <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

It is an access2000db
Table lost has the fields following:
lostID(longInt autonr, PK) | catID( LongInt, FK to objectCategory.CatID) | ObjectID (Text 200) | lostDate (Date/Time short) | kommuneNr (Text 4, FK to Kommuner.kommuneNr) | placeDetails (Memo)

Table objectCategory
CatID (PK, longInt) | CatType (text 200)

Table Kommuner
kommuneNr (PK, text 4) | kommuneNavn (text 50)

Please let me know if you need to know more, screenshots etc.. glad for all the help I can get <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

This is a portal for objects lost and found. You may also visit the beta at:
&lt;a href="www.taptellerfunnet.no/pages/start.asp"&gt;www.taptellerfunnet.no/pages/start.asp&lt;/A&gt;
My question refers to the seachfield located top right at the page
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Can you explain / lay out your table definitions. I sense something might be wrong with the joins - but will need to understand the relationships to work out what's going on here.

Also what database are you using?

Live the life you love
Love the life you live
~ ~ ~ ~ ~ ~ ~
<b>Simon Martin</b> - <i>DMXzone Manager</i>
<font size=1>[ Dreamweaver MX/MX2004 | ASP | SQL | XHTML/CSS | Web Accessibility ] </font id=size1>
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote><b></b>

Please anyone
I need to solve this problem.
Any suggestions wil be highly apreciated

Thank you!
-ole wegard