how select data mysql from 1 colums with many id different

Question

I have table a với colum Aid is PRIMARY KEY and table b with colum Bid is primary ky and colums Aids with 1 recode = 1 list Aid. example: table a aid |

I want to count for each bid how many aid( example b1 have 2 a1). i like use mysql but do not have the solution to this problem. who can help me

solution is a better database design. best would be to have a third table that holds the connections between a and b (1:n), then such a count is an easy task. — Jeff, Jun 10 '16 at 16:35
Normalize your data. Delimited columns aren't identifiers, they're just strings. SQL is great at querying structured data, not so much at string parsing and manipulation. — David, Jun 10 '16 at 16:35
It would be better practice to create a second table and store the comma-separated list in this table. Please read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) for more information on how and why. — Matt Raines, Jun 10 '16 at 16:36

score 1 · Answer 1 · answered Jun 10 '16 at 16:54

This is very bad database design! Firstly, you do not pass normal form one which is an instant red flag when designing a database. Secondly, you should never use lists in a database because you are repeating data that should not be repeated.

Solution:

Firstly, I would add an indexed ID to both of your tables. Then I would create an intersection table called 'AidsBid'. The intersection table will then use the ID's from both tables, which in turn will associate specific records with one another.

I would recommend reading up on normal form and practice designing some dummy databases on paper. This will ensure that future database you design will conform to NF rules and you will have an easier time designing your database.

score 0 · Answer 2 · answered Jun 10 '16 at 16:58

This is a great example of poor design leading to inefficient code. Yes, this can be done with queries inside a nested foreach loop, which can take longer depending on the size of your tables. It would be better practice, however, to add an associative table.

Please refer to the following link to learn about associative tables:

https://en.wikipedia.org/wiki/Associative_entity

If you have any questions, please feel free to ask.

score 0 · Answer 3 · answered Jun 10 '16 at 16:59

0

All design arguments aside, this will get your substring count:

SELECT ROUND ( ( LENGTH(Aids) - LENGTH( REPLACE ( Aids, "a1", "") ) ) / LENGTH("a1") ) AS count FROM `table b`

answered Jun 10 '16 at 16:59

Rob Wood

415
5
11

score 0 · Answer 4 · answered Jun 10 '16 at 18:03

Here a sample for you but its only a sample. better is to change your database design.

the query

SELECT b.*,
  CAST( MAX( ( IF ( a.Aid = 'a1' , ( LENGTH( CONCAT(b.Aids,',')) 
  - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
  / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a1,

  CAST( MAX( ( IF ( a.Aid = 'a2' , ( LENGTH( CONCAT(b.Aids,',')) 
  - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
  / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a2,

  CAST( MAX( ( IF ( a.Aid = 'a3' , ( LENGTH( CONCAT(b.Aids,',')) 
  - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
  / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a3,

  CAST( MAX( ( IF ( a.Aid = 'a4' , ( LENGTH( CONCAT(b.Aids,',')) 
  - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
  / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a4,

  CAST( MAX( ( IF ( a.Aid = 'a5' , ( LENGTH( CONCAT(b.Aids,',')) 
  - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
  / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a5

FROM table_b b
CROSS JOIN table_a a
GROUP BY b.Bid;

sample - the tables

MariaDB []> SELECT * from table_a;
+-----+
| Aid |
+-----+
| a1  |
| a2  |
| a3  |
| a4  |
| a5  |
+-----+
5 rows in set (0.00 sec)

MariaDB []> SELECT * from table_b;
+-----+-------------+
| Bid | Aids        |
+-----+-------------+
| b1  | a1,a1,a2,a3 |
| b2  | a3,a4,a5,a5 |
| b3  | a2,a3,a3    |
| b4  | a5,a1,a1,a3 |
| b5  | a3,a2,a1    |
+-----+-------------+
5 rows in set (0.00 sec)

**run the query **

MariaDB []> SELECT b.*,
    ->   CAST( MAX( ( IF ( a.Aid = 'a1' , ( LENGTH( CONCAT(b.Aids,','))
    ->   - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
    ->   / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a1,
    ->
    ->   CAST( MAX( ( IF ( a.Aid = 'a2' , ( LENGTH( CONCAT(b.Aids,','))
    ->   - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
    ->   / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a2,
    ->
    ->   CAST( MAX( ( IF ( a.Aid = 'a3' , ( LENGTH( CONCAT(b.Aids,','))
    ->   - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
    ->   / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a3,
    ->
    ->   CAST( MAX( ( IF ( a.Aid = 'a4' , ( LENGTH( CONCAT(b.Aids,','))
    ->   - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
    ->   / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a4,
    ->
    ->   CAST( MAX( ( IF ( a.Aid = 'a5' , ( LENGTH( CONCAT(b.Aids,','))
    ->   - LENGTH( REGEXP_REPLACE( CONCAT( b.Aids,','),CONCAT( a.Aid,',') ,'')))
    ->   / LENGTH( CONCAT( a.Aid,',')),0))) AS INT) AS a5
    ->
    -> FROM table_b b
    -> CROSS JOIN table_a a
    -> GROUP BY b.Bid;
+-----+-------------+------+------+------+------+------+
| Bid | Aids        | a1   | a2   | a3   | a4   | a5   |
+-----+-------------+------+------+------+------+------+
| b1  | a1,a1,a2,a3 |    2 |    1 |    1 |    0 |    0 |
| b2  | a3,a4,a5,a5 |    0 |    0 |    1 |    1 |    2 |
| b3  | a2,a3,a3    |    0 |    1 |    2 |    0 |    0 |
| b4  | a5,a1,a1,a3 |    2 |    0 |    1 |    0 |    1 |
| b5  | a3,a2,a1    |    1 |    1 |    1 |    0 |    0 |
+-----+-------------+------+------+------+------+------+
5 rows in set (0.00 sec)

MariaDB []>

-)

how select data mysql from 1 colums with many id different

4 Answers4