Mireille28 Mireille28 - 6 months ago 6
SQL Question

Getting information from another table in same query

actually i have 2 table the log table and the user table.

my current statement is working fine to get all logs in the inverse order

this is my current query

SELECT *
FROM ( SELECT * FROM logs WHERE roomid = '$room' AND post_id > '$last' ORDER BY post_id DESC LIMIT 100) AS log
ORDER BY post_id ASC


here are the 2 table structure

logs table

date | user_logs_id | message | type

user table

user_id | avatar | color | ....

the goal for me is to keep the same order as i actually have with the query above but getting information avatar and color from user table matching to the user_logs_id

then now my problem is that i have field user_logs_id in logs table and i want to retreive some information matching that user_logs_id from the user table

anyone can help me to solve that thanks

Answer

Assuming that user_logs_id = user_id simply use a join

SELECT logs.*, user.color, user.avatar FROM logs 
JOIN user on user.user_id = logs.user_logs_id
WHERE logs.roomid = '$room' AND post_id > '$last' ORDER BY post_id DESC LIMIT 100

**edit updated query to show specific fields

Comments