Select on 2 tables that contain same field name ?

Question

Hi,

I am developing a CMS that using database based on Joomla ! In Joomla db, we have 2 table :

+----------+
|Categories|
+----------+
id
title
...

+-------+
|Content|
+-------+
id
title
catid
...

I have a query below :

SqlQuery q = new Select("*")
                         //.Top("1")
                         .From(JosContent.Schema)
                         .InnerJoin(JosUser.IdColumn, JosContent.CreatedByColumn)
                         .InnerJoin(JosCategory.IdColumn, JosContent.CatidColumn)
                         .Where("catid").IsEqualTo(catId);

And in the ASPX page, I show data like that :

Tite : <%# DataBinder.Eval(Container.DataItem, "title") %>
In category : <%# DataBinder.Eval(Container.DataItem, "title") %> 
// Category tite not Content title, but ASP.NET think it is Content title :-(

Please help me fix it ? How to discean between that ?

Thanks alot !

Answer 1

You can refer to the title of Categories table by: Categories.title and the title of Content table by: Content.title. Sorry if I misunderstood your question.

Answer 2

In your select you could do like Alan said, and then use AS to change what you reference them as later. (I don't specifically know ASP, i'm a PHP programmer but I assume it fairly similary).

Something like

SELECT *, Categories.title AS categoryTitle, Content.title AS contentTitle ... ...

And then you can refer to categoryTitle or contentTitle.

Answer 3

Hi all,

I have finised it :)

SqlQuery q = new Select("*", "jos_Categories.title AS 'CatTitle'")
                         //Select("*", "CatTitle = jos_Categories.title")
                         //Select("*", "CatTitle = JosCategory.TitleColumn")
                         //.Top("1")
                         .From(JosContent.Schema)
                         .InnerJoin(JosUser.IdColumn, JosContent.CreatedByColumn)
                         .InnerJoin(JosCategory.IdColumn, JosContent.CatidColumn)
                         .Where("catid").IsEqualTo(catId);

Thanks ... Google :-)