MySQL Explode or Something

[JREAM]

Happy Holidays

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.

[r937]

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)

[JREAM]

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!)

[r937]

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?

[JREAM]

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

[r937]

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?

[r937]

(I also check this in the SQL).

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)

[JREAM]

Okay i will try doing this! Thanks

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

[r937]

cool i had no idea you cuold have 2 PKs LOL

lol indeed

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

for searches in the opposite direction