-1

I have tables like so:

TableA

id    id_link_list
1     1,2

TableB

id    info
1     blah
2     blah-more

I'm struggling to include the TableA.id_link_list column (which is already a comma separated list) into a SELECT IN () query

This works fine:

select GROUP_CONCAT(b.info separator "; ") as infolist 
FROM `TableB` as b 
where b.id in ('1,2')

But this doesn't.

select GROUP_CONCAT(b.info separator "; ") as infolist 
FROM `TableB` as b 
where b.id in (
    select id_link_list 
    from `TableA` 
    where id=1
)

What can I do to work around it?

  • fwiw - I dummied this dbfiddle up for you when I got this in the review queue before I noticed you'd self-answered. Consider reading this page for guidance on making your questions more answerable for others: minimal reproducible example – Peter Vandivier Jul 25 at 9:14
  • Storing comma separated values in a single column is a really bad idea. You just found one reason why. If you have the chance, fix your data model and refactor that to a proper one-to-many relationship – a_horse_with_no_name Jul 26 at 5:21
0

Ok so I just realized I could put the link column in TableB to solve the problem

TableA

id    
1     

TableB

id    info         tablea_id
1     blah         1
2     blah-more    1

Like so

select GROUP_CONCAT(b.info separator "; ") as infolist 
FROM `TableB` as b 
where b.tablea_id=1
  • 1
    I just realized I could put the link column in TableB You're cheater. – Akina Jul 25 at 19:05
0
SELECT GROUP_CONCAT(b.info separator "; ") infolist 
FROM `TableA` a, `TableB` b 
WHERE FIND_IN_SET(b.id, a.id_link_list)
0
where b.id in ('1,2')

does not work (unless id is the 3-character string "1,2"). You want

where b.id in (1,2)  -- efficient

or

where FIND_IN_SET(b.id, '1,2')  -- inefficient

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.