jream — 2010-12-24T10:34:32-05:00 — #1
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.
r937 — 2010-12-24T10:43:01-05:00 — #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)
jream — 2010-12-24T10:48:41-05:00 — #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!)
r937 — 2010-12-24T10:58:02-05:00 — #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?
jream — 2010-12-24T11:07:37-05:00 — #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
r937 — 2010-12-24T11:52:19-05:00 — #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?
r937 — 2010-12-24T11:54:04-05:00 — #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)
jream — 2010-12-24T11:57:30-05:00 — #8
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 — 2010-12-24T13:46:37-05:00 — #9
you might need an additional index on (test2,test)
for searches in the opposite direction