matching a single value against the field that contains comma separated value

[watsmyname]

hello,

 

i have two tables, portfolios and tbl_technology,

 

The rows in these table will be like this

 

portfolios (tech_id below is coma separated tech_id used in portfolio from tbl_technology)

 

pid  tech_id

1    2,3,4,5

 

tbl_technology

 

tech_id  technology_name

1            PHP

2            .NET

3            Flash

4            Photoshop

5            Ajax

6            Jquery

 

well my problem is... i m listing technology name from tbl_technology. And each row of listing has checkbox to select for deletion that technology. All i want to do is if portfolio table has that technology then this technology cannot be deleted. In above example row, tech_id field of portfolio table has  2,3,4,5 technologies, so these technologies from tbl_technologies cant be deleted. remaining 1 and 6 can be deleted. So i want to check whether certain tech_id of tbl_technology, say "2" is there in tech_id field of portfolio table say "2,3,4,5"...

 

Any help would be appreciated

 

Thanks

watsmyname

[kickstart]

Hi

 

Comma separated fields are a nightmare to connect up.

 

The best solution would be to split the tech_id column off from portfolios onto a seperate table, with one row per portfolio / tech_id combination. You could then write the php around that to work around a simple loop of the resulting data.

 

If you cannot change the design to do that then realistically you will have to do it using 2 seperate pieces of SQL (inefficient)

 

It is maybe possible to do a join from portfolios to technology based on the values in the CSV field, but it is not a pleasant thing to try and even if you succeed it will be very difficult to understand / maintain in the future.

 

Having said that, this will do it:-

 

SELECT z.pid, y.technology_name, y.tech_id, x.IndId
FROM Portfolois z
INNER JOIN tbl_technology y
LEFT OUTER JOIN (
SELECT DISTINCT a.pid, SUBSTRING_INDEX( SUBSTRING_INDEX( tech_id, ",", b.ournumbers ) , ",", -1 ) AS IndId
FROM Portfolois a, (
SELECT hundreds.i *100 + tens.i *10 + units.i AS ournumbers
FROM integers AS hundreds
CROSS JOIN integers AS tens
CROSS JOIN integers AS units
)b
)x ON z.pid = x.pid
AND y.tech_id = x.IndId

 

However it is not easy to follow for most people and I hate to think of the inefficiency. I strongly recommend against trying something like this unless you just want to see if it is possible.

 

It returns every possible combination of pid and tech_id / tech name, and an extra column which will contain the tech_id if that pid has that tech_id or NULL if it doesn't. It relies on a table called integers which has a single integer column called i with the values 0 to 9. It will cope with a pid having up to 999 tech_ids.

 

Results would be something like:-

 

pidtechnology_nametech_idIndId

1PHP1NULL

1.net22

1Flash33

1Photoshop44

1Ajax55

1Jquery6NULL

 

All the best

 

Keith

[watsmyname]

thanks for the help mate, i m gonna try this! thanks again

[fenway]

Not that I'm for comma-separated anything, but FIND_IN_SET() can be your friend.