douchunsui2395 2017-06-26 09:51
浏览 55
已采纳

MySQL - 使用具有不匹配行数的多个子查询的结果

I don't know if this is possible but I'm trying to use the value from a MySQL SELECT query in multiple subqueries following the advice given at How to specify the parent query field from within a subquery in mySQL?. There are unequal numbers of results in each subquery, which I suspect is why I can't get this working.

My DB is more complex than on the above post. I'm dealing with 5 tables which are as follows:

  1. substances - a list of chemical substances, all of which have a unique ID (substances.id)
  2. ecs - EC Number* values.
  3. cas - CAS Number* values.
  4. ecs_substances - Mapping substances.id to ecs.id. One substances.id may be mapped to one, or more than one ecs.id
  5. cas_substances - Mapping substances.id to cas.id. One substances.id may be mapped to one, or more than one cas.id

* These are terms used for classifying chemicals.

An example of how the data may look for 1 substance:

substances.id  | substances.name
-------------- | ----------------
1              | FooBar

An example of the assignment to multiple CAS numbers (the same principle applies for EC but am just using CAS for brevity):

cas_substances.id | substance_id
--------------------------------
997               | 1
----------------- | ------------
23423             | 1
--------------------------------

And then the CAS Numbers themselves:

cas.id | cas.value
------ | ---------
997    | ABC-123
------ | ---------
23423  | XYZ-876
------------------

The desired result is that I want to obtain a list of each substances.id, substances.name with a corresponding list of the CAS/EC Numbers, ideally formatted with a line break, e.g.

substances.id  | substances.name | cas_values         |
-------------- | --------------- | ------------------ |
1              | FooBar          | ABC-123 "
" XYZ-876

I understand that the logic to be as follows:

  1. SELECT * FROM substances
  2. SELECT * FROM cas_substances WHERE cas_substances.substance_id = Result(s) from 1 (substances.id)
  3. SELECT cas.value FROM cas WHERE cas.cas.id = Result(s) from 2 (cas_substances.cas_id)
  4. Formatting to handle the fact there may (or may not) be multiple cas_substances rows - line break between records if there is.

I don't understand how to write the SQL that gets Results from 1/2.

Is it even possible to do this in MySQL? The application I'm building is written in PHP but I figured using SQL only will be far quicker than doing multiple subqueries in PHP (although it is much easier to write in PHP).

  • 写回答

2条回答 默认 最新

  • dongmu1996 2017-06-26 10:00
    关注

    You can join these three tables together, then use group by and group_concat to achieve that.

    select
        `sub`.*,
        group_concat(cas.`value` separator ' "
    " ') as cas_values
    from substances `sub`
    left join cas_substances cs on `sub`.id = cs.substance_id
    left join cas on cas.id = cs.cas_id
    group by `sub`.id
    

    A demo here.

    Note: GROUP_CONCAT only works in mysql.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥85 maple软件,solve求反函数,出现rootof怎么办?
  • ¥15 求chat4.0解答一道线性规划题,用lingo编程运行,第一问要求写出数学模型和lingo语言编程模型,第二问第三问解答就行,我的ddl要到了谁来求了
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥15 maple软件,用solve求反函数出现rootof,怎么办?
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿
  • ¥100 关于使用MATLAB中copularnd函数的问题