Optimize SQL query to fetch file names

Question

I've two tables, the first table contains information on the ideas submitted by user and the second table contains information on the file attachments that are part of the idea. An idea submitted by the user can have 0 or any number of attachments.

Table 1:
-------------------------------------
Id Title Content Originator
-------------------------------------
1 aaa bbb John 
2 ccc ddd Peter

--------------------------------------

Table 2:
---------------------------------------------
Id Idea_id Attachment_name
---------------------------------------------
1 1 file1.doc
2 1 file2.doc
3 1 file3.doc
4 2 user2.doc
---------------------------------------------

Table 1 primary key is Id and table 2 primary key is Id as well. Idea_id is the foreign key in table 2 mapping to table 1 Id.

I'm trying to display all the ideas, along with their attachments in a html page. So what I've been doing is: get all the ideas from Table 1 and then for each idea record, retrieve the attachment records from table 2.It seems to be extremely inefficient. Could this be optimized so that I can retrieve idea records and their corresponding attachment records in one query?

I tried with left outer join(Table 1 left outer join Table 2) but that would give me three records for Id = 1 in table 1. I'm looking for a SQL query to club idea detail and attachment names in 1 row to make HTML page processing efficient. Otherwise, What would be the best solution for this?

SQL isn't really suitable for cases where you don't have a fixed number of records. You can build something that handles n columns with dynamic SQL by checking first how many there is etc. but that's going to be really complex compared to just building the wanted structure in php. — James Z, Nov 20 '16 at 08:48
If you want the file names for example in a comma separated list, search for "with xml path" usage — James Z, Nov 20 '16 at 08:56
Do you want this output? http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — M. Rezaeyan, Nov 20 '16 at 11:17
@ James Z, thank you for suggesting "with xml path". So far able to get file names separated with comma, trying to club table 2.Id as well in comma separated list — start2learn, Nov 20 '16 at 12:50

score -1 · Answer 1 · answered Nov 20 '16 at 08:57

-1

If you want to get all attachments along with all ideas, you may use GROUP_CONCAT. such as

SELECT *, (SELECT GROUP_CONCAT(attachment_name separator ', ') FROM TABLE2 WHERE idea_id = TABLE1.id) attachments FROM TABLE1

answered Nov 20 '16 at 08:57

Abdullah Al Shakib

2,034
2
15
16

That's for MySQL, this is SQL Server question – James Z Nov 20 '16 at 09:12

score -1 · Answer 2 · answered Nov 20 '16 at 09:01

I probably missed the point but a left join should bring back all the records

create table `ideas` (
    `id` int(10) unsigned not null auto_increment,
    `title` varchar(50) not null,
    `content` varchar(50) not null,
    `originator` varchar(50) not null,
    primary key (`id`)
)
engine=innodb
auto_increment=3;

create table `attachments` (
    `id` int(10) unsigned not null auto_increment,
    `idea_id` int(10) unsigned not null default '0',
    `attachment` varchar(50) not null default '0',
    primary key (`id`),
    index `idea_id` (`idea_id`),
    constraint `fk_ideas` foreign key (`idea_id`) references `ideas` (`id`) on update cascade on delete cascade
)
engine=innodb
auto_increment=5;



mysql> select * from ideas;
+----+----------------+-----------+-----------------+
| id | title          | content   | originator      |
+----+----------------+-----------+-----------------+
|  1 | Flux capacitor | Rubbish   | Doc             |
|  2 | Star Drive     | Plutonium | Professor Frink |
+----+----------------+-----------+-----------------+



mysql> select * from attachments;
+----+---------+------------------------------+
| id | idea_id | attachment                   |
+----+---------+------------------------------+
|  1 |       1 | Flux capacitor schematic.jpg |
|  2 |       1 | Sensors.docx                 |
|  3 |       1 | fuel.docx                    |
|  4 |       2 | plans.jpg                    |
+----+---------+------------------------------+


mysql> select * from ideas i
    -> left outer join attachments a on a.idea_id=i.id;
+----+----------------+-----------+-----------------+------+---------+------------------------------+
| id | title          | content   | originator      | id   | idea_id | attachment                   |
+----+----------------+-----------+-----------------+------+---------+------------------------------+
|  1 | Flux capacitor | Rubbish   | Doc             |    1 |       1 | Flux capacitor schematic.jpg |
|  1 | Flux capacitor | Rubbish   | Doc             |    2 |       1 | Sensors.docx                 |
|  1 | Flux capacitor | Rubbish   | Doc             |    3 |       1 | fuel.docx                    |
|  2 | Star Drive     | Plutonium | Professor Frink |    4 |       2 | plans.jpg                    |
+----+----------------+-----------+-----------------+------+---------+------------------------------+

Optimize SQL query to fetch file names

2 Answers2