mysql selecting

[a1amattyj]

Hi,

 

I have two tables which both contain 2 columns of the same name (race and gold). Im using an inner join mysql statement.

 

In table 1, .race is an id which corresponds to the second table where .race is a name. I would like to only select the name (table 2).

 

This is what im using:

 

SELECT `races`.race,

`members`.id,

`members`.username,

`members`.gold

FROM `members`

INNER JOIN `races`

ON `members`.race = `races`.idrace

 

However, as the table columns increase, im having to manually add them to the statement (`members`.username, ) for example say i add 'gold2' to the table, i need to add (`members`.gold2,)

 

Any better way to do this?

 

thanks

[jl5501]

Looking at what you have written, I think you may be going about this very inefficiently.

Can you post your table structures showing field names and data types and explain again what you are trying to do, and I will be able to advise on the best approach

[abazoskib]

that is actually the best way to do it. Dont be fooled by "SELECT * FROM ..." queries because they can lead to confusion later on.

[a1amattyj]

Table 1 Members

 

#  |  id    |  username  |  gold    |  race

1        6            example        50          3

 

Table 2 Races

 

#  |  idRace    |  gold    |  race

46      6345          5            Trees

 

 

Thanks

 

 

[jl5501]

ok, unfortunately I do not see anything in those 2 tables that can be used as a join field. When 2 or more tables are joined, they are joined by having 2 columns of the same data type having the same value.

 

eg an items table with an order_id of 2 can join to an orders table with an id of 2

 

but the fields must be the same type and have the same value

[a1amattyj]

Sorry I meant:

 

#  |  id    |  username  |  gold    |  race

1        6            example        50          3

 

Table 2 Races

 

#  |  idRace    |  gold    |  race

46      3          5            Trees

 

ok, unfortunately I do not see anything in those 2 tables that can be used as a join field. When 2 or more tables are joined, they are joined by having 2 columns of the same data type having the same value.

 

eg an items table with an order_id of 2 can join to an orders table with an id of 2

 

but the fields must be the same type and have the same value

[jl5501]

ok, that would work as a jon then, but I am a little uncertain as to what you meant when you mentioned adding other fields. If you find yourself needing to add a variable number of the same 'things' then you do not do it by adding columns to an existing table, you create another table to hold the 'things'. I may have misunderstood exactly what you meant when you talked about adding your gold2 etc, but perhaps you could clairify

[a1amattyj]

So say I added 'gold2' to table 1, i would then need to manually edit my mysql statement as per first post to also select it from the table.

 

Sorry I meant:

 

#  |  id    |  username  |  gold    |  race

1        6            example        50          3

 

Table 2 Races

 

#  |  idRace    |  gold    |  race

46      3          5            Trees

 

ok, unfortunately I do not see anything in those 2 tables that can be used as a join field. When 2 or more tables are joined, they are joined by having 2 columns of the same data type having the same value.

 

eg an items table with an order_id of 2 can join to an orders table with an id of 2

 

but the fields must be the same type and have the same value

[jl5501]

this is what I am asking.  Is your mention of gold2 the start of you wanting to add columns for gold3......n?

 

If so, then adding columns is not the way to go

[a1amattyj]

this is what I am asking.  Is your mention of gold2 the start of you wanting to add columns for gold3......n?

 

If so, then adding columns is not the way to go

 

As im developing the software and people request new features, im having to extend the members table to include the new stats. So say they wanted a new option for them to limit attachments, I would add on the table "attachmentlimit", so I would have to alter the statement.

[jl5501]

ok then in that case, this would certainly work for you.

 

select r.race from members m,races r where m.race=r.idrace

 

that is if all you want to get out is the race name from the races table.

You could add more fields to the select as needed.

 

I have used table aliases and an implicit join in that query, which can make it easier to read.