Use Mysql column in subquery of subquery

Question

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.

See normalisation. Or don't bother with an RDBMS

Strawberry
– Strawberry

2017-09-14 10:37:15 +00:00
Commented Sep 14, 2017 at 10:37 — Strawberry
– Strawberry, Commented Sep 14, 2017 at 10:37
First of all this is very bad design.

Ankit Bajpai
– Ankit Bajpai

2017-09-14 10:37:25 +00:00
Commented Sep 14, 2017 at 10:37 — Ankit Bajpai
– Ankit Bajpai, Commented Sep 14, 2017 at 10:37
@Strawberry how do you recommend to design?

ykay says Reinstate Monica
– ykay says Reinstate Monica

2017-09-14 10:39:36 +00:00
Commented Sep 14, 2017 at 10:39 — ykay says Reinstate Monica
– ykay says Reinstate Monica, Commented Sep 14, 2017 at 10:39
@AnkitBajpai how do you recommend to design?

ykay says Reinstate Monica
– ykay says Reinstate Monica

2017-09-14 10:39:46 +00:00
Commented Sep 14, 2017 at 10:39 — ykay says Reinstate Monica
– ykay says Reinstate Monica, Commented Sep 14, 2017 at 10:39

ykay says Reinstate Monica · Accepted Answer · 2017-09-14 11:48:13Z

1

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

answered Sep 14, 2017 at 11:48

ykay says Reinstate Monica

4,9224 gold badges36 silver badges44 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2017-09-14 12:11:06Z

0

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)
);

edited Sep 14, 2017 at 12:11

answered Sep 14, 2017 at 10:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

5 Comments

Strawberry Over a year ago

The first column is (probably) redundant, but if an autoincrementing id is used, then it must be defined as (a component of) a PRIMARY KEY.

ykay says Reinstate Monica Over a year ago

Yeah, it's definitely a way to go. I thought my way might make the query faster. Is there a way to get the subquery to use the field?

Strawberry Over a year ago

@ykay Amend your question accordingly.

Strawberry Over a year ago

@ykay Obviously, I mean once you've fixed your broken structure.

Gordon Linoff Over a year ago

@ykay . . . Ask another question, once you have fixed the data structure. Don't make significant revisions to a question after it has answers.

Collectives™ on Stack Overflow

Use Mysql column in subquery of subquery

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related