Multiple Selects - what's the best approach?

[MySqlRuben]

Hi!

I'm really new to MySql and I was wondering if someone could help get a better understanding of the mechanics of this language.

For example, I have two tables: one called 'articles' and other called 'autors'. What I want is to select all the columns from the 'articles' table, based on the article name, and then select all the columns from the 'autors' table, based on the autor of that article. So I came up with the following php code:

$article_name = $_POST['article'];
$query = "SELECT * FROM articles WHERE title='".$article_name."'";
$result = mysql_query($query);

if($result){
    while($row = mysql_fetch_array($result)){
        $search_autor = $row['autor'];
    }
    $query_autor = "SELECT * FROM autors WHERE name='".$search_autor."'";
    $result_autor = mysql_query($query_autor);
    if($result_autor){
        //echoes out all the results
    }
}

It works, but I'm worried about its efficiency, because after all it's two querys. Is there a way to, with just one single query statement, do it all? Also, could you point me to some article or site that talks about the different query statements?

 

[ESquared]

SELECT
   T.*
FROM
   autors T
   INNER JOIN articles A ON T.name = A.name
WHERE
   A.title = 'MyTitle'

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[SQLBill]

I suggest you post in the MySQL forum. While Microsoft SQL Server (this forum) and MySQL are similar - there are differences.

-SQLBill

Posting advice: FAQ481-4875

 

[MySqlRuben]

Thanks ESquared for the reply. I must say that I dont understand much of what you just posted. From what I'm understanding, I think that the statement selects all the columns from the autors table and from the articles table and then joins them (?) If you could explain: what the T and A means? Also, whats does the INNER JOIN and the ON do?

SQLBill, sorry about that. I just saw the Sql Server in the forum name and thought that this was the MySql forum. I'll be sure to post in the correct one the next time I post.

 

[SQLBill]

The "T" and "A" are alias' so he didn't have to keep typing the table names.

SELECT
   T.*
FROM
   autors T
   INNER JOIN articles A ON T.name = A.name
WHERE
   A.title = 'MyTitle'

is the same as

SELECT
   autors.*
FROM
   autors
   INNER JOIN articles ON autors.name = articles.name
WHERE
   articles.title = 'MyTitle'

-SQLBill

Posting advice: FAQ481-4875