CODEWITHSUNDEEP
mysqlsql
ykay says Reinstate Monica
ykay says Reinstate Monica

Reputation: 4854

Use Mysql column in subquery of subquery

I have two tables

Reports

id     consultants
1      1,2,3,4

Users

id    name
1     John
2     Tom

When I try to run this query I get an error: "Unknown column 'reports.consultants' in 'where clause'"

SELECT reports.id, 
(SELECT GROUP_CONCAT(name SEPARATOR ", ") from (SELECT name from users where users.id in (reports.consultants)) a) as consultant
FROM reports

I've thought of using a separate ReportConsultants table but I thought storing the consultants in the reports table may make the query more efficient and wondering if there is a way to do this. Using this kind of structure is also much simpler in the code.

Upvotes: 1

Views: 118

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

Fix your data structure! You should not be storing lists of ids in a string. Here are some reasons:

  • Numbers should be stored as numbers, not strings.
  • Ids should have declared foreign key constraints.
  • SQL's strength is not string functions.

The right way to represent the data is with a junction table, 

create table ReportConsultants (
    ReportConsultantId int auto_increment primary key,
    ReportId int,
    ConsultantId,
    constraint fk_reportconsults_report foreign key (reportid) references reports(reportid),
    constraint fk_reportconsults_consultant foreign key (consultantid) references consultants(consultantid)
);

Upvotes: 0

ykay says Reinstate Monica
ykay says Reinstate Monica

Reputation: 4854

Yes it is possible, the syntax needs to be slightly different

SELECT reports.id,
  (SELECT GROUP_CONCAT(name SEPARATOR ", ") from users where FIND_IN_SET(users.id, reports.consultants)) as consultant
FROM reports

Upvotes: 1

Related Questions