mysql joins maybe..?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
dimxasnewfrozen
Forum Commoner
Posts: 84
Joined: Fri Oct 30, 2009 1:21 pm

mysql joins maybe..?

Post by dimxasnewfrozen »

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
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: mysql joins maybe..?

Post by JAB Creations »

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

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)
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. :)
Post Reply