[Resolved] SELECT Like or something similar.

[ghjr]

Lets say I have a column with a bucnh of numbers seperated by comma's like this:

Column Projects:
2,38,45

How would I go about only selecting rows with the number 3 in this column? If I use LIKE %3% wouldn't that also select a row that had a 38 and not just a 3?

Cheers! 😃

[nemonoman]

This is fundamentally bad design. These values blelong as individual rows in a related table. You are going to have trouble if you continue this bad design.

The work around would be:

WHERE (somevalue LIKE '3,%'
OR somevalue LIKE '%,3,%'
OR somevalue LIKE '%,3'
OR somevalue LIKE '3')

as opposed

SELECT somestuff FROM myMainTable, myRelatedTable
WHERE MyRelatedTable.key=MyMainTable.key AND myRelatedTable.value=3

[ghjr]

Gotcha Nemonoman!

But even in my related table wouldn't I need to seperate the project numbers by commas (because the number of projects assigned to each key can vary)? I mean, I could make 10 different columns and put one project number in each, but what if I needed to assign 11 projects to a key?

Cheers! 😃

[nemonoman]

You'd make a table like

entityprojects

entityid
projectid

The entity id would be the key that you're currently using for the table currently storing the project info.

in your example, you'd popultate this table with 3 records

if entity id is 123:

your records would be:

123/2
123/38
123/45

SELECT entity.* FROM
entity, entityproject
WHERE
enitity.id=entityid
AND projectid=3

[AstroTeg]

Originally posted by ghjr


But even in my related table wouldn't I need to seperate the project numbers by commas (because the number of projects assigned to each key can vary)?

It looks like you have a 1 to many relationship between a project and these numbers.

You don't need additional columns. You need a second table to hold the relationship of pojects and numbers. You didn't define what a number is, so we're assuming its just that, a number. But if that number relates to something else in another table, then you'd have 3 tables you'd be working with.

Storing comma-delimited data in a field is a BAD idea and means you have a database design problem. Let the database work FOR you, not against you...

[ghjr]

Ops, forgot bout this post... but many thanks for the insights guys... I'll take that into consideration.

Cheers! 😃