PHP&MySQL: Combine more lines and put it in array

Question

Possible Duplicate:
mysql with comma separated values

I have two tables with more lines, like this:

1. numberstable
    ---------------------------
    | number_id | number_name |
    ---------------------------
    |    1      |     one     |
    |    2      |     two     |
    |    3      |     three   |
    |    4      |     four    |
                .
                .
                .
    ---------------------------

2. testtable
    -------------------------------
    | test_id | numbers | sthelse |
    -------------------------------
    |    1    | 2.4.5.6 | text1   |
    |    2    | 4.8.7.1 | text2   |
    |    3    | 2.7.8.5 | text3   |
    -------------------------------

First I would like to combine all three "numbers" rows from table "testtable" to get something like this: 1.2.4.5.6.7.8 and then exclude it in next query. This query is "SELECT number_id, number_name FROM numberstable ORDER BY number_name". After excluding I would like to show just numbers which aren't in use in "testtable" (9, 10, 11, ...).

How to do that?

What RedFilter means is: http://stackoverflow.com/questions/1258743/normalization-in-mysql — Bailey Parker, Aug 15 '12 at 15:51
Please, read [SQL Antipatterns](http://pragprog.com/book/bksqla/sql-antipatterns) book. You mess is covered in **first** chapter. — tereško, Aug 16 '12 at 08:27

score 1 · Accepted Answer · answered Aug 15 '12 at 15:55

If you are trying to relate the numberstable to the testable. I would think you would be much better served to add another table that would relate the two to where you had a schema like

1. numberstable
    ---------------------------
    | number_id | number_name |
    ---------------------------
    |    1      |     one     |
    |    2      |     two     |
    |    3      |     three   |
    |    4      |     four    |
                .
                .
                .
    ---------------------------

2. testtable
    ---------------------
    | test_id | sthelse |
    ---------------------
    |    1    | text1   |
    |    2    | text2   |
    |    3    | text3   |
    --------------------

3. numbersintesttable
    -----------------------
    | test_id | number_id |
    -----------------------
    |    1    | 2         |
    |    1    | 4         |
    |    1    | 5         |
    |    1    | 6         |
    |    2    | 4         |
    |    2    | 8         |
    |    2    | 7         |
    |    2    | 1         |
    -----------------------

So the new table would be a many-to-many join table that you could use to get all your needed data in a single query by utilizing the type of joins you want (INNER, OUTER, etc.)

Ok, I will use this solution, but how to eliminate already used number_id? If test_id 1 use number_id 2, then other test_id can't use number_id 2 anymore. How to do this with SQL? — user1257255, Aug 15 '12 at 21:14
@user1257255 That is more of a problem of your application logic than the database. In other words your application will need to be able to lookup the already used numbers and pick one that is not being used already. You can however enforce unique number_id values in the numbersintesttable by putting a unique index on the number_id field in that table such that there is no way to insert a duplicate number_id into that table. — Mike Brant, Aug 15 '12 at 21:21
What about some function in SQL like JOIN, to choose just unused numbers? — user1257255, Aug 16 '12 at 08:12
You could easily do something like `SELECT * FROM numberstable WHERE number_id NOT IN (SELECT DISTINCT number_id FROM numbersintesttable)` — Mike Brant, Aug 16 '12 at 15:34

PHP&MySQL: Combine more lines and put it in array

1 Answers1