Joining tables

[frog1010]

Hi guys basically i have a got a search application on a mobile phone, where the value entred by the user is pattern matched agianst the records in teh database.

i wnat five tables from my database to be patern matched to the input value, how do i do this??

how do i create a JOIN for 5 tables?? please help..been tryin to do this all day and cant figure it out

my sql query to search 1 table is:

SELECT Functions FROM MsgTble WHERE Names LIKE '*sub*'

the Functiosn holds the correct name of function in mobile such as onbox outbox etc the Names are teh alternatives the user can enter such as read message,new message, this should then bring up the result Inbox etc

PLease help!!!

[frog1010]

i still have not figured it out! my code is not working!!

PLease coudl someone help me...ive been racking my brains over this and cant find a solution!

Please!

[antares686]

First what database are you using and can you describe the individual tables and what fields you will bring back from each?

[frog1010]

hello thank you for your reply

im using ms access the columns names are the same in all tabels, i have got id, function and alternatives, basically as im tryin to make a search that allows users to enter in alternatives words for mobile phone functions,lets say, read message this would then bring up reulst of inbox, outbox etc.

i have therfore palced all the tables into one larger table

im now having a problem of creating teh sql statement this is what i have

SELECT MsgF,CAllRegF,ProfileF,SetF FROM MobileTble WHERE MsgN,CallRegN,ProfileN LIKE '%" +sub+ "%'

this works fine when im uisng one field name to be searched, but when i enter in all tables it doesnt work

i also wanted to know how i can make the results of the query dynamic, as in when the user can select a result and be taken to that page??as i then need to allow the user to go to the selected result..very confused as to this one. I would really love if any one can suggest to me how i can do this by exmaple code or something??


Im using JSP as the scripting lanugae, wml and ms Access

thank you for reply

[Zeb]

You say "Join", but I'm not really sure that is what you mean...

I will explain what I think you mean, and then give the solution, and you can correct me if I got it wrong...

You have 5 tables, each with the same columns, but different data, and you want to select data from all those tables and return them as one result set. Is this correct? If that is, then I think you are talking about doing a UNION.

Example: If I have two tables, with 2 columns each:

EmployeeTable:
Name   Phone
------------------
Zeb      1234
John     1235

FriendTable:
Name   Phone
------------------
NotPete#   9876
Nick            9875
Jim             9874

Now, I want to get all my friends or employess that start with "J", so I go:

SELECT * FROM EmployeeTable WHERE Name LIKE 'J*'
UNION ALL
SELECT * FROM FriendTable WHERE Name LIKE 'J*'

That will return:

Name   Phone
------------------
John     1235
Jim        9874

I think this what you mean.

Basically, a union appends the rows, a JOIN appends columns - and I am pretty sure you want UNION.

BTW: UNION ALL means append everything - even duplicates. UNION on it's own will remove any duplicate rows.

[frog1010]

Hi Zeb!!

Thank you, i have already sorted it out! But your were very helpful!!

Thanks alot
xx

[Zeb]

no worries mate - glad to be of some sort of help