Mari Cher Mari Cher - 5 months ago 11
SQL Question

Php - about SQL-query with WRONG(?) database

Easy question for you but hard solution for me.=)

I have a database - USERS. In USERS I have two tables - USERS_INFO and EVENTS.

USERS_INFO contain next fields:
user_id
user_name
...


and

EVENTS contains next:
event_id
user_id
obj_id (this element means, for examle, when user_1 will change information
event about user_2 in this table appears record like:
event_id=1
user_id=user_1
obj_id=user_2)


So, as you can see, information about user_id, obj_1 from table EVENTS I get from table USERS_INFO in field user_id.
I connected it.

The question is = how to create right query?
I need to see something like this:

user_1 user_4 some_event_like_edit (means that user_1 changed smth in user_4)

I can create query, but it works wrong. I did -

SELECT USERS_INFO.user_name, EVENTS.event FROM USERS_INFO,EVENTS WHERE USERS_INFO.user_id=EVENT.user_id


BUt I cant create query for another field obj_id!!
Result give me the fields where EVENTS.user_id=EVENTS.obj_id

What I should do??

Answer

You just need to join to user table twice, like this:

SELECT u_1.user_name as Who_Changed,u_2.user_name as Who_Got_Changed, e.event
FROM EVENTS e
INNER JOIN USERS_INFO u_1
 ON (u_1.user_id=e.user_id)
INNER JOIN USERS_INFO u_2
 ON (u_2.user_id=e.obj_id)