multi selections in one query, how?

[Ghz]

Hello there,
I'm building up my home page right now with php and I have alot of information that I want it to be in my homepage. I've made all of the requirements and it's working fine now but I feel it's very have to be loaded because of the queries in the homepage. What I'm thinking of is how to make multi selections in one query?!! That is to say, can I select different columns from different tables in one query???? And how???

one more thing, doesn't that over load my server as many of users surfing in my site? And how many queries do you think are suitable to be in one page?

Alot of questions need to be answered from my best friends
Any help would be highly appreciated in advance

[PHP John]

The short answer is, Yes.

The long answer is that you must (as far as I know) have a common field (foreign key) in the tables you want to select from. This is what makes a relational database "relational".

For example, lets create 2 fictional tables:

tblUsers

• fldUserId (Primary Key)
• fldUserName (Unique)
• fldUserPassword
• fldRegistrationDate

tblPosts

• fldUserId (Foreign Key, but not Unique)
• fldTopic
• fldDatePosted
• fldText

Now, to query and retrieve the fields from both tables based on the UserId we would write the query thusly [img]images/smilies/wink.gif[/img]:

SELECT * FROM tblUsers as U, tblPosts as P WHERE U.fldUserId = P.UserId;

HTH

[Ghz]

Thanx PHP John ;
Well, I've seen a piece of code in a book and was looking like this:

 
SELECT country.continent,country.countryname,city.cityname
from country.city WHERE city.countryID = country.ID 

I really didn't understand what you meant very well. Couldn't it be more easier than this way??? If you or anyone else would guide me any useful article talking about this I'll be appreciated.

What about the rest of my questions???

[Chris82]

Loosely based on the query you posted. Imagine you have two tables: country and city

For example country holds following data:

ID | name
1  | Europe
2  | America
3  | Africa

And city holds following data: The citie's name, an internal number and the country's number it is located in.

cityid  | name     | continent
1       | Berlin   | 1
2       | London   | 1
3       | Utah     | 2

Here the column "continent" references the column ID in the table continent.

If you use:

SELECT continent.name, city.name
FROM continent, city 
WHERE continent.ID = city.continen;

you check where the numbers in the column "id" in table "continent" exactly match the same number in the column "continent" in the table "city".

That query would return:

Berlin    Europe
London    Europe
Utah      America

Because the table "city" does not contain a city which is located in Africa (i.e. no 3 in the column continent) no rows for "Africa" are returned.