Jump to content

MySQL - Columns based on 2 Columns by Row


Recommended Posts

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


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


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 by thunderousity
emoticon instead of B
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...