• Sign in to Neowin Faster!

    Create an account on Neowin to contribute and support the site.

  • 0
Sign in to follow this  

[MySQL] Simple JOIN

Question

-Alex-    101

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

2 answers to this question

Recommended Posts

  • 0
bolerodan    1

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

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.