[El Forum]

[eluser]pmhart[/eluser]
I am trying to do the following:

$this->db->join("tab as t", "t.id = " . $tab_id);

And I get an error that says:

Unknown column '25501' in 'on clause'

Given the $tab_id=25501, CI creates a query like this

JOIN `tab` as t ON `t`.`id` = `25501`

I need that number to not have backticks! I can do it as a valid query manually in mySQL, any suggestions?

[El Forum]

[eluser]pmhart[/eluser]
anyone?

[El Forum]

[eluser]kurucu[/eluser]
It doesn't look like a valid query to me, because I can't see how it would differ from a Where clause, but I'm no expert. I'd have thought you need to specify another table and column.

Anyway. The answer, as usual, appears to lie in the documentation, which says that you can just pass a SQL string into the select function, specifying the last parameter as FALSE to stop CI including backticks for you. This means you'd have to stop using active record for that particular query as the query is somewhat non-standard.

If you got it working in mysql, it would be worth pasting the code here if you still have problems later.

[El Forum]

[eluser]pmhart[/eluser]
It works in SQL if I take out the backticks:

JOIN `tab` as t ON `t`.`id` = '25501'

[El Forum]

[eluser]sophistry[/eluser]
that's not a job for JOIN. use WHERE.

[El Forum]

[eluser]pmhart[/eluser]
Hmm, despite the "etiquette" of JOINs, it's valid to mySQL and does what I need.

I need to JOIN the "tab" table because it contains the submission ID. And tab attributes are sometimes referenced by a submission ID because the tab id was not added.

I can add tab to the FROM with a comma, but that query takes 8 seconds. Using a JOIN takes less than a split second.

[El Forum]

[eluser]sophistry[/eluser]
it's difficult to follow the logic without seeing code, but at the risk of seeming impertinent, i think there is a gap somewhere in the JOIN approach.

you sound like you know what you are talking about, but, really, what is preventing this?

Code:

$this->db->where('id', $tab_id);
$q = $this->db->get('tab');

seriously, i'm curious to learn if you have a speed trick involving JOIN as opposed to WHERE.

[El Forum]

[eluser]kurucu[/eluser]
What he's doing is joining a table that doesn't share a key with the table to which it is being joined, but does relate to the data.

e.g. we have business, divisions and people tables, where people belong to a division, and divisions belong to a business. Also, people belong to a business (in his model).

He is (I think) doing something like this:

Code:

SELECT * FROM people JOIN business ON business.id = 1 WHERE people.business_id = 1

It would work, but I question the model, even if I did make it up myself.

If this is what is being planned, then the manual will tell you how to turn off backticks for certain functions (e.g. select and query, I think).