[SOLVED] JOIN - making one list from 5 lists

[marmite]

Hi,

 

I have a table with 6 columns - an ID, and 5 columns of codes.

 

I need to run a query that says "if this code exists in any of these five columns, do this".

 

So, I could run a query with an OR statement somewhere... but what I'd really like to do is JOIN the columns, so that I end up with a list of IDs and the codes all in one column.

 

So i have this:

 

ID    Code1    Code2      Code3      Code4    Code5

1        32          33            34          35          36

2        65          36            54          83          32

 

And i want this:

 

ID    Code

1        32

1        33

1        34

1        35

1        36

2        65

2        36

2        54

2        83

2        32

 

Scratching my head.. grateful for any help

Emma

[pikemsu28]

mysql> select id, code1 as code from test2 union
    -> select id, code2 as code from test2 union
    -> select id, code3 as code from test2 union
    -> select id, code4 as code from test2 union
    -> select id, code5 as code from test2 order by id;
+----+------+
| id | code |
+----+------+
|  1 |   32 |
|  1 |   33 |
|  1 |   34 |
|  1 |   35 |
|  1 |   36 |
|  2 |   65 |
|  2 |   36 |
|  2 |   54 |
|  2 |   83 |
|  2 |   32 |
+----+------+
10 rows in set (0.03 sec)

 

this is a long/basic way to do it.  maybe you can get an idea from this.

[fenway]

The "correct way" is to normalize this table

[marmite]

Thanks for this. @Fenway - by normalise do you mean that i shouldn't have five columns, but one?

 

@ anyone: I tried to complicate matters to JOIN another table and got an error because the column "code" is not recognised:

 

select mixed_id,mixed_name,mixed_msg, mixed_card_1 as code from cu_card_mixed as m
union 
select mixed_id,mixed_name,mixed_msg, mixed_card_2 as code from cu_card_mixed as m
union
select mixed_id,mixed_name,mixed_msg, mixed_card_3 as code from cu_card_mixed as m
union
select mixed_id,mixed_name,mixed_msg, mixed_card_4 as code from cu_card_mixed as m
union
select mixed_id,mixed_name,mixed_msg, mixed_card_5 as code from cu_card_mixed as m
inner join cu_cards as c on c.card_id=m.code
where mixed_status=1

 

Is there any way round this or do I need to do a nested query?

 

Thanks

Emma

[marmite]

Done it. Thanks for your help.

[fenway]

Thanks for this. @Fenway - by normalise do you mean that i shouldn't have five columns, but one?

That's right... you need a 3rd table linking each set of codes.