-Alex- 101 Posted December 21, 2008 Hey No matter how many JOIN tutorials I read, I can't seem to figure this one out. I have the following query at the moment: SELECT UNIX_TIMESTAMP(`stamp`) AS `stamp`, `user`, `lad`, `device`, `source` FROM `reports_given` ORDER BY `stamp` DESC Which produces a result like this: stamp user lad dev source --------------------------------------- 1229800341 1 3 5 4 1229697725 1 3 5 8 1229689512 5 1 5 4 1229688196 1 3 5 4 1229687695 5 1 4 8 1229683884 1 1 5 8 1229683873 5 3 4 8 1229626166 1 1 5 13 1229626148 1 1 4 13 1229612723 1 3 5 2 I also have a table called users that looks something like this: id user ---------- 1 Joe 2 Yup 3 Bob Now, what query can I run to replace the user ID in the first query with the name of the user from the second query? I'll give you a cookie if you can manage to get it for me and make me understand it :p Share this post Link to post Share on other sites
0 bolerodan 1 Posted December 22, 2008 I'm not sure if this is what you are looking for... i just roughly re created your tables so i could easily copy and paste the output for you. Before we go on, im sorry about the horrible uneven padding. I'm too lazy to fix it, but I'm sure you can figure it out. reports_given table +--------+------+------+--------+ | userid | lad | dev | source | +--------+------+------+--------+ | 1 | 3 | 5 | 4 | | 2 | 1 | 3 | 6 | | 3 | 2 | 1 | 1 | +--------+------+------+--------+ User table +----+------+ | id | user | +----+------+ | 1 | bob | | 2 | jill | | 3 | Yup | +----+------+ my query SELECT u.user, r.lad, r.dev, r.source FROM reports_given r INNER JOIN user u ON u.id = r.userid; im just joining two tables, defining them with a character (u for user table, r for reports_given table, mainly just created Aliases for the tables) to distinguish between them, and then joining on the userid of the reports_given table, to the id on the user table. notice im selecting the user column from the user table (u.user) to display the username Might be a better way of doing it, but seems to work for me.. output is as given +------+------+------+--------+ | user | lad | dev | source | +------+------+------+--------+ | bob | 3 | 5 | 4 | | jill | 1 | 3 | 6 | | Yup | 2 | 1 | 1 | +------+------+------+--------+ Share this post Link to post Share on other sites
0 Popcorned1 0 Posted December 22, 2008 SELECT UNIX_TIMESTAMP(`stamp`) AS `stamp`, `users`.`user`, `lad`, `device`, `source` FROM `reports_given` LEFT JOIN `users` ON `reports_given`.`user` = `users`.`id` ORDER BY `stamp` DESC Share this post Link to post Share on other sites
Hey
No matter how many JOIN tutorials I read, I can't seem to figure this one out. I have the following query at the moment:
Which produces a result like this:
I also have a table called users that looks something like this:
Now, what query can I run to replace the user ID in the first query with the name of the user from the second query?
I'll give you a cookie if you can manage to get it for me and make me understand it :p
Share this post
Link to post
Share on other sites