Complicated Query or Simple???

[jbsb1106]

I really need help with a query I am trying to make. This query is kicking my butt. I have not been working with mysql for very long. I am still somewhat of a "noob"

ok a litte bit of background on this query I am trying to come up with.

I have two tables that I am trying to get certain things from each.
Here is my basic table structures:

Table 1:
clans_gametypes
this table has 4 fields in it.
id, db_field_name, desc, image_name
this table has 42 rows of data in it.

Table 2:

clans_games_played
this table has 44 fields.
id, clan_id, and 42 other fields that are named the same as the 42 rows of data that are in the field db_field_name in table 1.

the db_field_name field has 42 rows of shortened game names that are the same as 42 of the fields in the second table.

The thing that I need to do is get all the info for the field image_name data in Table 1 that = "yes" AND clan_id =(a variable that I have from another query) in Table 2 in the 42 shortened game name fields that are in Table 2.

I hope I explained what I need good enough for someone to understand what I want...lol

I thank you in advance for you time...Thanks

 

[cian]

I'm a total "noob" but I found this book extremely helpful.
Check out the free chapter, it's very useful:

http://www.oreilly.com/catalog/mysqlckbk/chapter/index.html

É ::

http://www.endamcg.com

 

[mgould00]

if the id field is the same in both tables you can do this:

select * from clan_gametypes inner join clans_games_played on clan_gametypes.id = clan_games_played.id where clan_gametypes.(whatever field)='yes' and clans_games_played.clan_id='(whatever)'

this will give you all the data that that meets the criteria you have described. if the tables do not have a link, you can't really pull the data you need.

hope this helps...

 

[swampBoogie]

From what I can glimpse of your description I think that you need to redesign your data model. As soon as you talk about having row values matching column names you are on the wrong track.

 

[jbsb1106]

Thanks all. I decided to redesign my data model. I am going to take the clans_games_played and make it a little simpler. I am going to take all the fields that equivilate to all the game names and delete them and add 2 fields. I am going to make 2 fields in that table one called game_played and another called game_id. I think that maybe then I will be able to make this work
Only problem is that if I do it that way it will be inserting 42 rows of data into that table for each clan if they happen to play all 42 games

Does that sound like a good idea to you ?
Does any one have a better idea or is that good enough?

Thanks
jbsb1106

 

[jbsb1106]

Oooops...I just thought about something.
Maybe I won't need the one called game_played. Just the one called game_id.

jbsb1106

 

[swampBoogie]

It sounds good to me.

 

[jbsb1106]

Thnx SwampBoogie.

I was thinking about the idea for a little bit, but I was dreading changing things up.

You helped to nudge me into it..lol.

I got it done and all is working well.
Iwill post a link to the site when I get it launched and all bugs worked out if any. Just in case any one wants to visit it and critique it.

Thanks again all

jbsb1106