[SOLVED] JOIN, AS,

[xstevey_bx]

I am really new to mySQL and have been trying to get this working all day but I just cant get my head round the concepts im reading. I was wondering if someone would solve my problem so I have a way to relate the code to what im doing to help give me named examples rather than the table1 and table 2 once I have come across

 

I have a 'news' table

idcategory_idnews article

11article text

21article text

33article text

41article text

54article text

61article text

 

and a 'news_categories' table

 

idnamedescription

1Blogblog description

2Newsnews description

3Updatesupdates description

4Otherother description

 

I have all the information stored in a database and want to SELECT * FROM 'news' WHERE news.category_id = news_categories.id AND news.category_id = $_GET['category']???...

 

but when the values are put into an array I want a column name to be created 'category' and I want its value to be the 'name' column from 'news_categories' = 'category_id' for that particular articles id?

 

(SELECT name.news_categories AS category WHERE news.category_id = news_categories.id)??

 

Thanks for any help

[Barand]

SELECT n.id, n.category_id, n.news_article, c.name as category

FROM news n

    INNER JOIN news_categories c ON n.category_id = c.id

ORDER BY category

[mbeals]

a join is fairly easy to think about when you look close at the syntax.  First when working with joins, we have to explicitly tell mysql which table to deal with.  So our select statement needs to be:

 

 

Select news.*, news_categories.* .....

 

or since we don't really care to use the id's:

 

Select

news.`news article`,

news_categories.name, news_categories.description  ....

 

This says our result 'table' will have those three columns (news, article. name and description).

 

Now we need to join the tables together.  Since news.category_id will (or should) always correspond to a entry in news_categories, we will just use a generic join:

 

Select

news.`news article`,

news_categories.name, news_categories.description

FROM news, news_categories

WHERE news.category_id = news_categories.id

 

Now we just need to limit the search to a particular category:

 

Select

news.`news article`,

news_categories.name, news_categories.description

FROM news, news_categories

WHERE news.category_id = news_categories.id

AND news.category_id = '$category'

 

Since you want to rename stuff, we need to use the AS statement.  You want the news_categories.name column to be called 'category'

 

Select

news.`news article`,

news_categories.name AS category, news_categories.description

FROM news, news_categories

WHERE news.category_id = news_categories.id

AND news.category_id = '$category'

[xstevey_bx]

I have solved my problem It outputs as I like, I have added slight modifications to allow me to refine the news results and allow sorting.

 

$query  = " SELECT news.*, news_categories.name AS category

FROM news, news_categories

WHERE news.category_id = news_categories.id

AND news.category_id IN ($category)

ORDER BY $key $order

LIMIT $offset, $toplimit";

 

Is that a technically correct query to use?