CODEWITHSUNDEEP
mysqlsqlnode.js
Umesh Sehta
Umesh Sehta

Reputation: 10683

How to use join in sql for get desired result

I have 2 tables orc_users and orc_files. Now I want to fetch files of related users from orc_files table I have try this Query:-

use orc 
select * 
from orc_users as  T1 
INNER JOIN orc_files AS t2 on T1.id = t2.userid
where T1.email='sdfsdf';

and I got this result:- enter image description here

but I want records something like:-

user={name:"sad",
      phone:"asdasda",
      files:[{filename:"dfsdfs",size:12,fileid:"sdfs"}, 
      {filename:"dfsdfs",size:12,fileid:"sdfs"}]}

I am using node.js with MySQL so it give me result in array.

please help me. Thanks in advance.

Upvotes: 2

Views: 66

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35573

all untested: Use group_concat() for the comma separated string and group by in the query, like so:

select
        t1.id
        /* specify the fields here */

      , GROUP_CONCAT(T2.Filename
             ORDER BY T2.Filename ASC SEPARATOR ', ') as filenames

from orc_users as  T1 
INNER JOIN orc_files AS t2 on T1.id = t2.userid
where T1.email='sdfsdf'
group by
      t1.id
      /* specify the fields here too */
;

Try also using CONCAT() to get both filename and size, e.g.

      , GROUP_CONCAT( CONCAT(T2.Filename,' size:',T2.size)
             ORDER BY T2.Filename ASC SEPARATOR ', ') as filenames

If concat() inside the group_concat() works [I think it will] keep adding items until you get all the details required.

performance warning: no idea how well this would perform, might not be great.

Upvotes: 1

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

There is no possible to get that result in a single query, but you can get files data in each individual row as query and attach it results into current row.

Upvotes: 0

Related Questions