-1

How to join 2 tables in the following case? Case is so complicate, I am not sure how to ask. Anyway that is my 2 tables. Please have a look.

This is album_tb

enter image description here

This is cart_tb

enter image description here

In "cart_tb", I want to add "album_images" field within album images of "album_tb" according to "album_id".

So I want the result as following.

enter image description here

How to join 2 tables?

SatelBill
  • 641
  • 2
  • 12
  • 28
  • 1
    try `FIND_IN_SET` for the same – Dark Knight Oct 08 '20 at 17:42
  • This should demonstrate why you shouldn't store lists of id's in comma-separated strings. See [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad/3653574#3653574) – Bill Karwin Oct 08 '20 at 17:50
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Oct 08 '20 at 18:20

1 Answers1

1

you can use group_concat to concatenate album_images

select c1,c2,c3, group_concat(c4, ',') as album_images from 
(
  select
    ct.col1,
    ct.col2, 
    ct.col3,
    SUBSTRING_INDEX(SUBSTRING_INDEX(ct.album_id, ',', numbers.n), ',', -1) albumId
  from
    (select 1 n union all
     select 2 union all select 3 union all
     select 4 union all select 5) numbers INNER JOIN cart_tb ct
    on CHAR_LENGTH(ct.album_id)
       -CHAR_LENGTH(REPLACE(ct.album_id, ',', ''))>=numbers.n-1
  order by
    id, n
) ct
inner join album_images ai
  on ai.rel_col = ct.rel_col
group by c1,c2,c3
Derviş Kayımbaşıoğlu
  • 28,492
  • 4
  • 50
  • 72