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?
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.)