Select two results at the same time

[Aeonix]

I think this is called "foreign key" and "local key" ?

I have a table users, these users have rank. It's an int.
There is a table named rank, this table has field where hexadecimal string is placed FF0000. Is there a way to SELECT * FROM users and SELECT * FROM rank WHERE id=users.rank... or something?

[RudyM]

What are you trying to do? Is there something in rank that you would like displayed in your user results? Like

SELECT * 
FROM USERS U 
LEFT JOIN RANK R ON R.RANK = U.RANK

? This would display all from USERS and RANK tables joined by the rank column.

By the way, we would need more details about the tables to determine whether rank is a foreign key. My guess would be as follows:

USERS table has columns including rank.
RANK table has list of ranks with rank as a primary key and other columns, perhaps a description.

In this case, the foreign key would be USERS.rank. FOREIGN keys reference a PRIMARY key from another table. You may read more about this here: http://www.w3schools.com/sql/sql_foreignkey.asp

[Aeonix]

Okay, maybe different way, something else that could help me understand this and morph into my needs.

I list forums. From table forums The results are Name|Description|LastAuthorID.
The results are for example Forum1|Description1|1.

I have table users. And I'd like to get name of the user of the ID of LastAuthorID.
Something like SELECT * FROM users WHERE id=LastAuthorID. For each matched result.
So that I will have name of forums, description of forum, id of last poster, name of last poster, rank of last poster (etc. etc. etc. etc.)

I tried this:
SELECT * FROM forums U LEFT JOIN members ON R.lastTopicAuthor = U.id
errors in:
#1054 - Unknown column 'R.lastTopicAuthor' in 'on clause'. Swapping id and lastTopicAuthor doesn't help.

Also, the link you provided, I've been there, but I can't understand jack out of it, that's why I came here.

[hielo]

try:
SELECT * FROM forums AS F INNER JOIN users AS U ON F.lastAuthorID = U.id

To help you understand ... forums AS F simply makes F an alias for forums. Similarly, ... users as U makes U an alias for users. The AS keyword is optional, so it could have been written as
SELECT * FROM forums F INNER JOIN users U ON F.lastAuthorID = U.id and it still would have worked. You are not required to "alias" the table names, so you could have also written it as SELECT * FROM forums INNER JOIN users ON forums.lastAuthorID = users.id. Notice that the ON clause uses the actual table names since there are no prefixes.

[Aeonix]

Yes! Now it's much clearer, now let me read this 5 times and take about 1 minute to process each word, then I'll understand it ;)

But thanks. As you explain it I may understand it fluently soon (I have more grasp on it now).

[Aeonix]

One more question though.

SELECT * FROM forums INNER JOIN users ON forums.lastAuthorID = users.id
requests all data from both tables. How could I request id, name only from users for example?

LEFT JOIN seems to be key (I'm searching).

[RudyM]

Selecting only the table's columns as follows:

SELECT users.ID, users.NAME FROM forums INNER JOIN users ON forums.lastAuthorID = users.id

[Aeonix]

OH! I thought the first SELECT XYZ was about forums, not about users.

And this other way around :P? How do I select specific elements of both sides?
Let's say users.ID, users.NAME and forums.NAME, forums.DESCRIPTION.

Sorry for making you do this, it's just, tutorials don't explain it pretty well, the only text I found useful around the net is couple posts above by hielo.

Oh and is it possible to mash more than 2 tables? Like 3, 4, 5? That could make for a killer query, instead of 5 different ones.

[RudyM]

Yes, that's correct. As @hielo pointed out, you could give your tables aliases so that you don't have to write out the whole table name. Also, one more thing to point out, you may want to defferentiate between NAME in USERS and FORUMS, so you'll have to alias the columns:

SELECT u.ID, u.NAME AS USER_NAME, f.NAME AS FORUM_NAME, f.DESCRIPTION
FROM forums f
INNER JOIN users u ON f.lastAuthorID = u.id

[hielo]

How could I request id, name only from users for example

In general, you would just need SELECT tableName.columnName, otherTableName.columnName FROM ....

The order in which you list the columns/fields after the SELECT doesn't matter. So you could do:
SELECT user.id, user.name, forums.lastAutherID FROM forums INNER JOIN users ON forums.lastAuthorID = users.id or SELECT forums.lastAutherID, user.id, user.name FROM forums INNER JOIN users ON forums.lastAuthorID = users.id.

As a matter of fact, just because you can have a JOIN between/among tables it does not mean you are required to select a field from evey joined table. As an example, the following selects only from user table even though there is an inner join with forums:
SELECT user.id, user.name FROM forums INNER JOIN users ON forums.lastAuthorID = users.id.

Oh and is it possible to mash more than 2 tables

Yes. Just add more join clauses ON the related column. For example, on your original post, forums.lastAuthorID is related to user.id. So if you have a other tables with fields user_id that are also related to user.id, then your query would be:

SELECT * FROM (forums INNER JOIN users ON forums.lastAuthorID = users.id
                                             (INNER JOIN thirdtable ON thirdTable.user_id = user.id)
                                             (INNER JOIN fourthtable ON fourthTable.user_id = user.id)
                             )

[Aeonix]

PERFECT.

And the AS helps too (with u.NAME AS USER_NAME), I can command SQL to give me what I want, instead of taking time to parse it in PHP.

Thank you everybody, through reusing and rereading the logic, I will learn a lot! This is WAY better than making 5 different requests and receiving tons of useless data.