mysql query two row from two tables

Question

I have this Query to call information form two table.

DB::get("SELECT friends. * , (SELECT `login` FROM `users` WHERE `users`.`id` = friends.`user_id`) AS `login` FROM `friends` WHERE `id_user`='" . $this->user['id'] . "' ORDER BY `id` DESC LIMIT ")

So if user is on friends list show username , i would like to get username and avatar. Avatar row is avatar . I try with this.

DB::get("SELECT friends. * , (SELECT `login`, `*avatar*` FROM `users` WHERE `users`.`id` = friends.`user_id`) AS `login` FROM `friends` WHERE `id_user`='" . $this->user['id'] . "' ORDER BY `id` DESC LIMIT ")

And give me the error

SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

where is the mistake?

See answer in this possible duplicate of SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s). sql query explaining that you cannot have more than 1 column returned and to use a join instead. — Nope
– Nope, Commented Sep 25, 2017 at 16:22
When you select multiple columns and try to assign on a single column name it never works and should be that way, as it does not make sense, think it... And mysql engine thought about it and had added an exception for this and it throws that error. Now how do we overcome as you can see some answers suggested usage of JOIN, so try using JOIN by yourself and let us know if there is some issue. — Abhik Chakraborty
– Abhik Chakraborty, Commented Sep 25, 2017 at 16:41

Ravi · Accepted Answer · 2017-09-25 16:24:16Z

2

First of all you should use Prepared Statement and Second, you can't write inline view, which has two columns

SELECT friends. * , (SELECT `login`, `*avatar*` FROM ..

instead you should use JOIN, which might be efficient than current approach and more readable.

edited Sep 25, 2017 at 16:24

answered Sep 25, 2017 at 16:16

Ravi

31.6k44 gold badges124 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Martinovska Over a year ago

LEFT JOIN ? ?

Ravi Over a year ago

@Martinovska That depends. Left Join will return common and data from the left table. I think, in your case, you should use INNER JOIN

Martinovska Over a year ago

Where is the problem in my code? About Prepared Statement?

Ravi Over a year ago

@Martinovska I have posted a link, please refer that, to understand, what does prepared statement means

Martinovska Over a year ago

Thank you @Ravi , but I did not understand you.

Darshan Mehta · Accepted Answer · 2017-09-25 16:14:44Z

2

You need to use JOIN, e.g.:

SELECT f.*, u.*
FROM friends f JOIN users u ON f.user_id = u.id
WHERE f.id_user = <your_id>
ORDER BY id DESC LIMIT <your_limit>;

answered Sep 25, 2017 at 16:14

Darshan Mehta

31k14 gold badges75 silver badges111 bronze badges

Collectives™ on Stack Overflow

mysql query two row from two tables

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related