[SOLVED] Associating tables

[txmedic03]

Okay here is the problem, I have multiple tables of data which must be associated with each other.  The tables do not have the same number of entries as the entries from table number 2 will each have multiple associations with data from table number 1.  I need to be able to search and return results from table number 1 and the associated data from table 2 and be able to limit the data from the results based on a WHERE clause that affects either table number 1 or table number 2.

 

I know I am being extremely vague, but I am not permitted to release any of the code and I'm sorry because I know how difficult this makes it on anyone who would attempt to help me, but I just really need a generic example I can build on.  Any help is greatly appriciated and if need be I can make up a faux example that would help me to do what I need to do.

 

Thanks in advance,

James

[benjaminbeazy]

http://dev.mysql.com/doc/refman/5.0/en/join.html

[btherl]

Table users

 

id  name

1    Alice

2    Bob

 

Table hitpoints

id    hp

1    50

3    20

 

SELECT name, hp FROM users JOIN hitpoints ON (users.id = hitpoints.id)

 

This will match up the tables by id, allowing you to match names from one table with hit points from the other.

[fenway]

I'd recommend explicit table prefixes, but yes.

[txmedic03]

okay so JOINs will work then?  I was under the impression that if you joined two tables with a JOIN statement that each table had to have an index to match the other one.  A 1 to 1 ratio.

 

So if I have 2 tables like this

 

table users

 

id name

1 Alice

2 Bob

 

table score

 

id user score game

1  1      100  Galaga

2  1      300  Galaxian II

3  2      394  Duck Hunt

4  1      483  Duck Hunt

5  2      499  Super Mario Bros

6  2      349  Galaxian II

7  2      394  Galaxian

8  1      932  Super Mario Bros

9  1      291  Galaxian

 

Then I can use  a "SELECT * FROM users JOIN score ON (users.id = score.user) WHERE score.user = 1 ORDER BY game ASC"

 

Right?

[benjaminbeazy]

should be able to

[txmedic03]

Okay I'll back-up the files and try it this way and let you know if it works.

[txmedic03]

Well, I haven't fully tested it, but after a complete db re-arrangement and a lot of re-coding it all seems to be working so far.  Only time will tell though.