[MySQL] Simple JOIN

[-Alex-]

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

[bolerodan]

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 |
+------+------+------+--------+

[Popcorned1]

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