Nsryjdtyk

I'm sure a very basic question, but I'm continue to be stuck:

Table A - image_number, camera_type, total_sales

Table B - image_number, keyword

Table A has one ROW for each image_number - example:

image_number="AXJ789, camera_type="Nikon", total_sales=678

image_number="JIJ123", camera_type="Canon", total_sales=999

image_number="KNI908", camera_type="Sony", total_sales=565

Table B has many ROWs for each image_number - example:

image_number="AXJ789", keyword = "rain"

image_number="AXJ789", keyword = "mountain"

image_number="AXJ789", keyword = "grass"

image_number="AXJ789", keyword = "cloud"

What I'm trying to do is JOIN the two tables so that I can generate the following output:

image_number="AXJ789", camera_type=678, camera_type="Nikon", keyword(1) = "rain", keyword(2) = "mountain", keyword(3) = "grass", keyword(4) = "cloud"

In other words, I want to have all items in each ROW in table A + all the items from table B. For each image_number in Table A, there could be no "keywords" in Table B or 50 keywords - depends on the image.

When I do an INNER JOIN, of course I can get one "keyword" from table B, but I can't figure out how to get all of them.

You can generate a comma-separated list of keywords for each image using GROUP_CONCAT and JOIN (but use a LEFT JOIN if an image may have no keywords).

SELECT a.*, GROUP_CONCAT(b.keyword) AS keyword_list

FROM a

JOIN b on b.image_number = a.image_number

GROUP BY a.image_number

Output for your sample data:

image_number    camera_type     total_sales     keyword_list

AXJ789          Nikon           678             rain,mountain,grass,cloud

Demo on dbfiddle

You can then parse this into an array in your application, for example in PHP (if you have read the row into $row):

$keywords = explode(',', $row['keyword_list']);

print_r($keywords);

Output:

Array

(

    [0] => rain

    [1] => mountain

    [2] => grass

    [3] => cloud

)

You can concatenate the keywords together:

select a.*,

       (select group_concat(b.keyword)

        from b

        where b.image_number = a. image_number

       ) as keywords

from a;

This creates a comma-delimited list of the keywords. This is much simpler (in MySQL) than trying to put them in separate columns. In fact, if you wanted separate columns, I might suggest parsing this result:

select a.*,  -- or whatever columns you want

       substring_index(keywords, ',' 1) as keyword1,

       substring_index(substring_index(keywords, ',' 2), ',', -1) as keyword2,

       substring_index(substring_index(keywords, ',' 3), ',', -1) as keyword3,

       substring_index(substring_index(keywords, ',' 4), ',', -1) as keyword4

from a left join 

     (select b.image_number, group_concat(b.keyword) as keywords

      from b

      group by b.image_number

     ) b

     on b.image_number = a. image_number;