MySQL Explode or Something

Databases
1

Happy Holidays :slight_smile:

I have a question because I don’t know if this is possible in MySQL alone, if it is I might restructure some tables.

1: If I have a row with this:

VARCHAR "1,2,3,4,5"

2: I do a simple sub-query:

SELECT	*
FROM	otherTable
WHERE	id = 12
AND			id NOT IN	(
	
	SELECT lists FROM firstTable
	WHERE id = 12

)

Q: Instead of comparing items NOT IN the string of “1,2,3,4,5” —
Is there a way to make those values separate so you can compare it with 1 then 2 then 3, etc?

I realize this might cause 10x more work on MySQL’s part, but I’ll only be using this for one set of items at a time.

2

could you redesign the table?

storing multiple values in a single column breaks the rule of first normal form

it makes your queries unnecessarily complex, not to mention slow (and hard to light)

3

On Christmas Eve the man of the hour lurks, HAHA.

I am just wondering because, this seems like it’s wasting space to have a table for “additional users”.

+----+-------------+----------------+
| id | assign_user | assign_project |
+----+-------------+----------------+
| 16 |           1 |              4 |
| 26 |           3 |              1 |
|  3 |           2 |              4 |
| 29 |           4 |              1 |
|  5 |           4 |              2 |
| 28 |           2 |              1 |
|  7 |           0 |              0 |
|  8 |           2 |              0 |

I guess its not a huge deal, I just thought there was a smarter way to do it – haha (There probably is, but A_SIMPLE_LAYOUT and DESIGN are very different things!)

4

first of all, the additional users table should ~not~ have its own id column

secondly, i’m having a real hard time connecting this table with what you posted in the beginning

what are you actually trying to do?

5

I was trying to select all the users that are not assigned to a given project so that the person doesn’t attempt to add a user already in there from a drop down list (I also check this in the SQL).

I only put the ID there because I thought I had to have a Primary Key. I’ve only used PK’s and UNIQUE keys, and this has two rows that BOTH would have to be unique – maybe I should combine those into 1

6

you mean columns, not rows – both columns together need to be the primary key

but what about your comma-separated list of values? where’s that?

7

you shouldn’t, you should just issue the INSERT and trap any error messages i.e. “duplicate key”

twice as efficient (i.e. 100% better) :wink:

8

Okay i will try doing this! Thanks :slight_smile:

I didnt make the column with the example "1,2,3,4’ i was just thinking about it wehn i woke up

cool i had no idea you cuold have 2 PKs LOL that will work nice

create table xxx (
 test int(11) not null,
 test2 int(11) not  null,
 primary key(test, test2)
)
9

lol indeed :slight_smile:

you might need an additional index on (test2,test)

for searches in the opposite direction :slight_smile: