How to write this SQL statement in Active Record

[eluser]Isamtron[/eluser]
Hello,

How to write this SQL statement in Active Record:

Code:

$this->db->query('SELECT a.slogan, a.brief, u.id, u.name FROM example_tbl AS a, users AS u WHERE u.user_id = "' . USER_ID . '"');

Please help. Thanks.

[eluser]WanWizard[/eluser]
I'm not sure you want to. This query contains two tables, but no join criteria. So the result will be a match of every record of a with every record of u. Is that intentional?

[eluser]Isamtron[/eluser]
Hi,

My code returns only one row because of the WHERE clause I have.

[eluser]WanWizard[/eluser]
Read the query, and then read it again. There is no join in this query. If you don't see the error, may I suggest to read up on designing queries?

Code:

mysql> use test;
Database changed

mysql> select * from users;
+---------+----+--------+
| user_id | id | name   |
+---------+----+--------+
|       1 |  1 | user 1 |
|       2 |  2 | user 2 |
+---------+----+--------+
2 rows in set (0.00 sec)

mysql> select * from example_tbl;
+---------+--------+
| slogan  | brief  |
+---------+--------+
| slogan1 | brief1 |
| slogan2 | brief2 |
+---------+--------+
2 rows in set (0.00 sec)

mysql> SELECT a.slogan, a.brief, u.id, u.name FROM example_tbl AS a, users AS u WHERE u.user_id =1;
+---------+--------+----+--------+
| slogan  | brief  | id | name   |
+---------+--------+----+--------+
| slogan1 | brief1 |  1 | user 1 |
| slogan2 | brief2 |  1 | user 1 |
+---------+--------+----+--------+
2 rows in set (0.00 sec)

Urhm... I really see 2 rows in the result set here...

[eluser]Isamtron[/eluser]
Hi,

users table primary key "id" not "user_id". example_tbl has a field called user_id as well.

[eluser]WanWizard[/eluser]
All fine, but your query has 'user_id' in the WHERE clause. And your query refers to a user_id field in the users table, and an id field in the example_tbl table. So what is it?

It's very difficult answering questions if the question isn't correct, don't you think?

In the assumption that there are no more suprises, and the field is called user_id, the query should be

Code:

$this->db->query('SELECT a.slogan, a.brief, u.user_id, u.name FROM example_tbl AS a JOIN users AS u USING (user_id) WHERE u.user_id = "' . USER_ID . '"');

or

Code:

$this->db->select('a.slogan, a.brief, u.user_id, u.name');
$this->db->from('example_tbl a');
$this->db->join('users u', 'a.user_id = u.user_id'); // standard AR doesn't support USING
$this->db->where('u.user_id="'.USER_ID.'"');
$query = $this->db->get();

[eluser]Isamtron[/eluser]

It's very difficult answering questions if the question isn't correct, don't you think?

Yet, you were able to help me

Your last solution worked. Thank you very much.

[eluser]glopv2[/eluser]
As an added bonus, if you know you're only hunting for 1 result you could use "LIMIT 1" to let mysql stop searching once it finds the 1st match.

[eluser]Isamtron[/eluser]
Thanks for the tip.