Welcome Guest, Not a member yet? Register   Sign In
MySQL question: 2 queries in 1
#1

[eluser]codex[/eluser]
Imagine 2 tables: 1) tbl_user and 2) tbl_user_hobbies.

Normally you (or at least I) would do a query to get all users and with the results I would make a loop to query the hobbies associated with the users. But is it possible to do it in one query, for instance with a subquery or join? And if so, how would one do this?

The question is really: is it technically possible?
#2

[eluser]Alex007[/eluser]
Code:
$this->db->select('tbl_user.userid');
$this->db->select('tbl_user.username');
$this->db->select('tbl_user_hobbies.hobby');
$this->db->from('tbl_user');
$this->db->join('tbl_user_hobbies', 'tbl_user.userid = tbl_user_hobbies.userid', 'LEFT');
#3

[eluser]Pygon[/eluser]
I can't specifically answer for CI. The sql would be like:

SELECT * FROM tbl_user LEFT JOIN tbl_user_hobbies ON tbl_user.id = tbl_user_hobbies.user_id;

edit:

I think Alex covered it.
#4

[eluser]xwero[/eluser]
Mysql only has subqueries from version 5 but many other databases have it i believe.

But what you are trying to do is something like
Code:
select id,username from tbl_user;
select hobby_id from tbl_user_hobbies where user_id=id;

I don't know if it can be done without redundant data using one query but it's possible with a join
Code:
select t1.username,t2.hobby_id from tbl_user as t1 right join tbl_user_hobbies on t2.userid=t1.id;
#5

[eluser]codex[/eluser]
Thanks guys, but if you do this query you get a 1 dimensional array like this (example):

user_1=> soccer,
user_1=> knitting
user_2=> knitting
user_3=> soccer
user_3=> knitting
user_3=> partying

But I'm looking for something like

user_1=> soccer, knitting
user_2=> knitting
user_3=> soccer, knitting, partying

See what I mean?
#6

[eluser]Pygon[/eluser]
I don't think this can be done easily, afaik, although I've not done alot of advanced SQL. Basically, you're attempting to return two rows of the same column as seperate columns of the same row.

The prior does give you the ability to do sorting without querying the database repeatedly.
#7

[eluser]codex[/eluser]
[quote author="Pygon" date="1194308601"]I don't think this can be done easily, afaik, although I've not done alot of advanced SQL. Basically, you're attempting to return two rows of the same column as seperate columns of the same row.

The prior does give you the ability to do sorting without querying the database repeatedly.[/quote]

That's what I was thinking too. Thanks!
#8

[eluser]Alex007[/eluser]
It IS really easy to do, look into the GROUP_CONCAT aggregate from MySql, it does exactly that.
#9

[eluser]codex[/eluser]
[quote author="Alex007" date="1194318729"]It IS really easy to do, look into the GROUP_CONCAT aggregate from MySql, it does exactly that.[/quote]

I've read it, but I'm afraid I don't understand. I'm getting stuff like [BLOB - 4 Bytes]. Can you show an example using the tables described above?




Theme © iAndrew 2016 - Forum software by © MyBB