[SOLVED] Alternative to JOIN

[JSHINER]

I have the following tables:

 

Table A

ID | NAME | A | B | C | D | E | F

1  |  John | 1 | 5  | 3 | 3 | 5 | 6

 

 

Table B

 

ID | NAME

1  | Name 1

2  | Name 2

3  | Name 3

4  | Name 4

5  | Name 5

6  | Name 6

 

---

 

Now correct me if I'm wrong, but I can't do a JOIN where table_a.a = table_b.id, table_a.b = table_b.id, right?

 

Is there any way I can do this?

[fenway]

I'm not sure what you mean... what's the desired output?

[JSHINER]

Desired output would be: John - Name 1 - Name 5 - Name 3, etc...

[fenway]

Desired output would be: John - Name 1 - Name 5 - Name 3, etc...

Shouldn't be a problem... you just need to join table b as many times as you have columns for A-F (just be sure to use a different alias for each)...

 

e.g. select a.name, ba.name, bb.name, bc.name

from tableA as a

inner join tableB as ba on ( ba.id = a.a )

inner join tableB as bb on ( bb.id = a.b )

inner join tableB as bc on ( bc.id = a.c )

 

etc.

[JSHINER]

Am I creating a.name,  ba.name, etc.

 

Sorry I had two ID and NAME columns - confusing. Let's try again with:

 

Table A

ID | NAME | A | B | C | D | E | F

-------------------------------------

1  |  John | 1 | 5  | 3 | 3 | 5 | 6

 

 

Table B

 

ID_CAT | Category

---------------------

1        | Apple

2        | Banana

3        | Orange

4        | Plum

5        | Cookie

6        | Coffee

 

With output of: John - Apple, Cookie, Orange, Orange, Cookie, Coffee

 

Sorry, first time with inner joins 

[fenway]

select concat( a.name, ' - ', concat_ws( ', ', ba.Category, bb.Category, bc.Category )
from tableA as a
inner join tableB as ba on ( ba.id_cat = a.A )
inner join tableB as bb on ( bb.id_cat = a.B )
inner join tableB as bc on ( bc.id_cat = a.C )