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:
2: I do a simple sub-query:
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.
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)
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!)
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?
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
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?
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)
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)
you might need an additional index on (test2,test)
for searches in the opposite direction