FULL TEXT + JOIN

[Luca Prete]

Goodmorning everybody,

I'm new to the forum, and maybe also a little bit new with MySQL. I know more or less basical things, but, in this case I've found lots of difficults. I tried to docuemnt my self about this problem before writing you, but of course I didn't find anything usefull. I think this should be one of the best places where to ask. Let me introduce the problem:

I've two tables (I simplify structure removing unusefull fields)

- users: id | name | surname

- friendships: idRequest | idReceive | status

I've also defined a first full text index in users for name and surname

When a user has to search for a new friend everything goes correctlly with this query:

SELECT id, name, surname, city, img,
MATCH(name, surname) AGAINST('*$friend*' IN BOOLEAN MODE) AS importance
FROM users
WHERE MATCH(name, surname) AGAINST('*$friend*' IN BOOLEAN MODE)
ORDER BY importance DESC

Now, the thing is: I would have the following situation: when a person make a research for a new friend a list of people appear (thanks to the first query). On each line that appears in the page there's a button. I would see a button called "Add friend" if I don't have any relation with the person (no records in friendships) or see a button called "Waiting for.." when there's already a request in friendships (status=1).

I know the request id from a session variable let's say called "myId"

My tried to create indexes on a view but I discovered it's impossible....

Do you have any idea?

Thanks previously for your help, and sorry for my English!!


Luca Prete