table join

Anon

Hi, I really need help getting my head around table joins.

maybe if you help me with this one I might be able to fathem it out.

News

article
client_ID

clients

ID
company_name

What I am doing is dispaying the news article and then i want to put a link below to allow a search on other articles by this company
e.g Click here for more news from xyz Company.

So I need to basically display the company name where the (clients)ID matches the (news)client_ID.

Anon

Try this

SELECT news.article
FROM news, clients
WHERE news.client_ID = client.ID
AND client.company_name = 'xyz';

This will display all the news for a given company name.

Hth

JBL

Anon

Hi thanks for your help, but its not quite what I wanted.

It might be easier if i showed the code so you can see whats going on.

I got it working but it listed all the company's names and not just the one relevant to the article.

the first sql call is ok(this was done by someone no longer in the company) but its the $company_sql call im having difficulties with.

$sql = "select news.*, NewsImages.Name from news left join NewsImages on NewsImages.ID = news.ImageID where Status='A' and news.ID=$newsid";
$result = mysql_query($sql);
echo "<TABLE width=\"100%\">\n";
while ($newsdetails = mysql_fetch_array($result)) {
#$color = ($color == $altcolor1) ? $altcolor2 : $altcolor1;
echo "<TR valign=top bgcolor=\"$color\">\n";
echo " <TD>" . $newsdetails["NewsHeadline"] . " ";
echo "(" . date("d-M-y", $newsdetails["DatePosted"]) . ") ";
echo nl2br($newsdetails["NewsArticle"]) . "</TD>";
echo "<TD>";
if ($newsdetails["Name"] != "") echo "<IMG src=\"$BigImageDir" . $newsdetails["Name"] . "\">\n";
echo " </TD>";

echo "</TR>\n";  
$company_sql = "select * from clients LEFT JOIN news ON clients.ID=news.client_ID";

    $companyresult = mysql_query($company_sql);

        while ($company = mysql_fetch_array($companyresult)) {

        echo "Display more articles related to " . $company["company_name"] . "";

        }

}

Anon

Panic not ! I have worked out where i was going wrong. And just to share my info to any else who is having problems, this is how I fixed it.

select * from clients LEFT JOIN news ON clients.ID=news.client_ID where news.ID=$newsid

$newsid was the orginal call to the news article, basiclly I wasn't telling the sql call to be specific to this articfle, therefore it was showing clients names from all news entries.

Many thanks JB for trying to help though