Categories > TinyButStrong general >

Using a LEFT JOIN with TBS

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: xxryexx
Date: 2006-02-11
Time: 18:10

Using a LEFT JOIN with TBS

I am fairly new to TBS and actually fairly new to using JOIN statements in my sql, but from all the research I did I can't seem to figure out how to use my results from the LEFT JOIN.

From what I noticed in other peoples queries they use unique names for their fields for each table table, for example maybe a users table they would have the id field be u_id. which works out when they JOIN because all the fields are different. I was wondering if there was anyway around this, usually you would just clarify the table and the field like users.id but TBS won't let you do that.

I hope this makes sense. Maybe the answer is that I just need to start naming my fields better.

Thanks in advance,
By: Skrol29
Date: 2006-02-12
Time: 11:34

Re: Using a LEFT JOIN with TBS

Hi,

This as nothing to do with TBS. TBS only send the query "as is" to the database system.

When you have several tables in an SQL query, you can alias them in order to precise which fields you mean.

Exampe :
SELECT a.id, b.id AS id_b, a.name FROM table_a AS a LEFT JOIN table_b AS b ON (a.id=b.id)
SQL need prefixe fields with table alias only if it can be ambigious.

This is available for all SQL 92 (it's a standard) database system.
By: xxryexx
Date: 2006-02-12
Time: 12:50

Re: Using a LEFT JOIN with TBS

Hi Skrol29. Thanks for the reply, but the problem I am having is using the result in the TBS template page. So after I made the query for example:

SELECT * FROM table_a AS a LEFT JOIN table_b AS b ON (a.id=b.id)

Now in both tables say I have a field 'name', how do I use a.name in the TBS template? If I simply use 'name' I will get b.name's result because it is the last field on the query called 'name'.

I am sorry, this probably doesn't make much sense and like I said the problem can probably be fixed by putting setting up my tables better like calling all my fields tablea_id instead of just id. Thanks again for your help.
By: xxryexx
Date: 2006-02-12
Time: 12:58

Re: Using a LEFT JOIN with TBS

To help clearify here is the php and the html scripts to see what I am talking about.

The call to the database:
$TBS->MergeBlock('blk1',$cnx_id,"SELECT * FROM table_a AS a LEFT JOIN table_b AS b ON (a.id=b.id)");

Results on the html template.
<ul>
  <li>[blk1.a.name;block=li]</li>
</ul>

That returns an error, because it is looking for $a['name'] but I need i am looking for 'a.name' from the query.

maybe that clears it up a little?
By: Skrol29
Date: 2006-02-13
Time: 11:50

Re: Using a LEFT JOIN with TBS

Hi,

Ok, I understand better now.
"a.name" is the name of the field returned by MySQL. But the dot (.) is also part of the TBS syntax for array items. That's why TBS doesn't recognize your field. Thus, for the moment, you have to use Aliases in your query in order to have names without dot. Sorry for that.

$TBS->MergeBlock('blk1',$cnx_id,"SELECT a.id AS id, a.caption AS caption FROM table_a AS a LEFT JOIN table_b AS b ON (a.id=b.id)");