Help querying related data from TWO tables in mySQL db 1

[Medar]

I have a database I am setting up that has basically two separate tables:

- a membership table where a person's "member name" should be a unique identifier...
- an alias table where a single member (from the above table) might have 2-5 "character" names - or aliases he may use. His "member name" will be tagged to each "alias name" in this table - but the member name is NOT unique here...the alias is.

I currently have all this information in the tables, but I cannot get it to relate and display - I know it is a function of SELECT * ... but beyond that I cannot get the information to display out at all! I am trying to do the following...

MEMBERNAME - ALIAS - EMAIL ADDRESS - CITY - STATE

MEMBERNAME is from the member's table
ALIAS is from the alias table
EMAIL ADDRESS is from the ALIAS table
CITY is from the members table
STATE is from the members table

I should be able to tie the two member fields together in each table...but just don't know what I am doing wrong. I have the following fields in the respective tables:

MEMBER TABLE
- m_autonum
- m_membername
- m_city
- m_state

ALIAS TABLE
- a_autonum
- a_alias
- a_membername
- a_email

ANY help is certainly appreciated!

 

[sophisticate]

This question should probably be in the MySQL section, but I'll answer it here anyway...

You use a LEFT JOIN. This allows you to combine columns from different tables based on common attributes between rows. Perhaps the command will allow a clearer visualization:

SELECT membertable.m_membername as member, aliastable.a_alias as alias, aliastable.a_email as email, membertable.m_city as city, membertable.m_state as state from membertable left join aliastable on membertable.m_membername = aliastable.a_membername <where statement>

This will pull your rows of data with formatted columns member, alias, email, city and state. To use the data in your program, you might incorporate a membername based hashtable with two keys reflecting arrays for the aliases and emails, and the other keys referring to the appropriate strings or create a new PHP class for each member with aforementioned properties.

Hope this helps,

brendanc@icehouse.net

 

[rycamor]

Medar,

Pay attention to the above post, especially about the hash table. It's an important method for building a truly relational database system.

See the following links for a couple of good tutorials: Database Normailzation, Part I and II.

http://www.devshed.com/Server_Side/MySQL/Normal/

http://www.devshed.com/Server_Side/MySQL/Normal2/