I have two tables:
The first table has a bunch of ID's and descriptions
The second table has values that match the first tables ID. This also has a user name to each value.
I want to display all of the values in table one, and only display the user name when table2 ID = table 1 ID and username equals a certain variable...
For example I want the output to look like...
Table 1 ID | Table 1 Description | Table 2 User | Table 2 ID
1 Page 1 user1 1
2 Page 2
3 Page 3 user1 3
4 Page 4
5 Page 5 user1 5
6 Page 6
7 Page 7
8 Page 8 user1 8
mysql joins maybe..?
Moderator: General Moderators
- JAB Creations
- DevNet Resident
- Posts: 2341
- Joined: Thu Jan 13, 2005 6:44 pm
- Location: Sarasota Florida
- Contact:
Re: mysql joins maybe..?
Let's say you were doing a simple loop to display forums and that visually you wanted the forum title and the name of the user who last posted in that forum.
General Discussion - Bob
Health - Sally
Off-Topic - Frank
I'm using a LEFT JOIN here because it's not an issue in this scenario if there are no posts/threads in a forum (empty forum). However there are scenarios where you do not want to return something because it has to have an association to be valid which is when you want to use an INNER JOIN.
Using AS creates an alias, look at the aliases I use for the two tables. Start by adapting the query I've posted for you and taking the name of your first table that you are selecting from and replace the forum_forums table in my query..and fix the AS alias too. Then just repeat with the joined table and it's alias...just keep doing a CTRL+F in notepad or whatever you use for temporary editing outside your main editor.
You can change the SELECT statement to use * to see everything that you can possibly choose from however in a live environment you'll want to only SELECT what you really need otherwise you'll waste resources.
Hope this helps.
General Discussion - Bob
Health - Sally
Off-Topic - Frank
Code: Select all
SELECT ff.forum_forums_name,ua.user_username FROM forum_forums AS ff LEFT JOIN user_accounts AS ua ON (ff.forum_forums_id_last_author_id=ua.user_id)Using AS creates an alias, look at the aliases I use for the two tables. Start by adapting the query I've posted for you and taking the name of your first table that you are selecting from and replace the forum_forums table in my query..and fix the AS alias too. Then just repeat with the joined table and it's alias...just keep doing a CTRL+F in notepad or whatever you use for temporary editing outside your main editor.
You can change the SELECT statement to use * to see everything that you can possibly choose from however in a live environment you'll want to only SELECT what you really need otherwise you'll waste resources.
Hope this helps.