0

I have a table segments with column comp_id, segment_id.

comp_id: 1
segment_id: 23,11,223,3331;
comp_id: 2
segment_id: 231,11,223,3331;

I have another table: comps with segment_id as well.

comp_id: 1
segment_id: 11, 3331
comp_id: 3
segment_id: 7, 63

I need to filter out rows from comp where all segment_id values are inside segment_id from segments table.

This is a simplified version of what I need to do. simple JOIN statements wouldn't help, I need to do this inside WHERE clause.

i.e.

WHERE
comp.segment_id = ALL (SELECT segment_id FROM segments)

this won't work because this compares single "comp.segment_id", I need all "comp.segment_id"s compared.

Is there any function or some method of doing this? And btw performance is crusial. Is there any intersect methods to do so? To simplify I need to find subsets of data. Actual query is very big and I won't post it here, don't give me JOIN examples, please.

Improve this question
  • simply don't store data in comma separated coumns, normalize your tables and do a proper selec with IN clause, else you need a function or procedure that loops through all comp. section id and searches if the exists – nbk Jan 13 at 15:56
  • What version of MySQL are you using? What you want is conceivably possible using RECURSIVE CTEs - but you need to be on 8... PostgreSQL would make this very easy - but hey... By subset, do you mean upper and lower bounds or inclusion of the actual values? – Vérace Jan 14 at 14:27
  • My fault, it's misleading. I use MariaDB 10.5.8. Because of performance issues, I decided to do this part in PHP, I think it will work faster. Hope question won't be closed, I'm still interested. @nbk That's what I thought, but are you sure there is no better way of doing this? – temo Jan 15 at 17:51
  • 1
    comma seperated column have to be soplit to cheklc them againbst the second columns with find_in_SET. a spölit function doesn't existi in mysql or maradb, because we use since year normalized databse design. so look for solitting and use that with FIN_IN_SET – nbk Jan 15 at 18:50
  • That Idea came to me on PHP but I went the regular way, with loops. This is a good suggestion tho, in every iteration I could concatenate value to some varialbe. Good answer my friend, TY. – temo Jan 16 at 9:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.