MySQL - What JOIN should i use? 2 Tables, Not the same number of members

Question

so i have 2 tables: -char (keeps information about the player) -special (its a rank for top mobs, so each line from this table is a record of all the players that killed a mob and the total time)

here's a summary i wrote:

##################
## table char: ##
#################
`char_id` - int(12) auto increment - primary key
`type` - varchar(18)
`color` - varchar(16)
---------------------------------------------------
#####################
## table special: ##
#####################
`id` - auto increment - primary key - just for keeping a number of events
`name` - varchar(24)
`time` - int(12) - the time the char's spent to kill the mob
`member1_id`- here is the char_id, it goes from 1 to 20 (until member20_id)
its a int(12), default is '0', so if there's only 3 members i would have something like:
`member1_id` = 1111111
`member2_id` = 2222222
`member3_id` = 1113123
`member4_id` = 0 (same for the rest)
---------------------------------------------------

i want to show every char from the member1_id, member2_id, memberN_id (a reminder: some values have 0 in member_id because there's no player, so no data to show) and order by TIME

here's a example:

Mob rank:

1 - Name: XYZ Guild - Time: 3 minutes
Member 1: 1111111 - Type: soldier - Color: blue
Member 2: 2222222 - Type: soldier - Color: red

2- Name: The FOO Guild - Time: 19 minutes
Member 1: 238381 - Type: Noob - Color: green
Member 2: 4444444 - Type: soldier - Color: red
Member 3: 549595 - Type: soldier - Color: green

the main thing its about the type of JOIN i should use, im kind like confused of how i can only get the data from where member_id is not 0 and also order by the TIME from SPECIAL

thank you very much

Answer 1

You have a problem in the design of your database. Table "special" should be two tables, "special" and "special_members". "Special" should have the columns "id", "name", and "time". "Special_members" should have "special_id", "member_id" and optionally a "member_order" column (if it's important who's number 1, number 2, etc) and "id" autonumbered column.

With this normalized structure, you query will become easy.

Without normalizing, you will need to use an INNER JOIN repeated 20 times and UNIONed together.

Answer 2

I think you need to rethink your table structure. Having member_1, member_2, member_3, member_4 is just going to cause more problems in the long run. Especially when your trying to relate data.

I'd propose you have the chars table like it is.

Keep the "special" table, but move the members into their own table and relate them to the special table by the id.

so for members you'd have

special_id, member

Now you can use an inner join and get all the data you want.