MySQL - Columns based on 2 Columns by Row

[thunderousity]

I have 2 tables.

A table full of items with an ID number and various values (Table A) and a table which works as the basis for an Item comparison using the ItemID's (Table B   )

Table A

ItemID, Item_Name, Value_1

1, Potato, 50

2, Carrot, 45

3, Broccoli, 35...

Table B

ComparisonID, ItemID1, ItemID2

1, 1, 3

2, 2 ,3

3, 5, 145

Requirement:

I want to return the ItemID, Item Name and Value for each row in Table B like so

Results:

ItemID1, Item_Name, Value_1, ItemID2, Item_Name, Value_1

1, Potato, 50, 3, Broccoli, 35

2, Carrot, 45, 3, Broccoli, 35

5, Courgette, 102, 145, Banana, 274

 

I can create the basic MySQL to return each of the columns using a Union query.

However I don't think this is the way to go. It's the part where I need to iterate through each row in Table B whilst also using separate ItemID's for separate columns from the same row that I can't get my head around.

Table B will only be up to 1000 rows so it doesn't need to be too efficient.

Any ideas where to start? Cheers.

Edited November 15, 2017 by thunderousity
emoticon instead of B

[justsomeguy]

You need to use a join, not a union.  You join the tables on their common ID and return the values you want from each table.