I have this membership site which has several membership tables. The tables are as follows.
The members table contains a primary key, member firstName, lastName, email, and a switch for active/inactive
The memberInfo table contains a primary key, memberID (to tie the records to a member) phone, address, secondary e-mail and so on.
The businessInfo table contains a primary key, memberID, business name, business contact info and other columns for business information.
The studentInfo contains a primary key, memberID, collegeAttending, graduationDate and so on.
MemberCards also contains a primary key, memberID, and memberCardNumber column.
It's a fairly easy task to combine the records using a query statement like this:
This works great if all members have something in all tables but many members do not have a business, do not have student info, and have not been issued member cards.
"SELECT members.*, memberInfo.*, businessInfo.*, studentInfo.*, memberCards.*
FROM members, memberInfo, businessInfo, studentInfo, memberCards
WHERE memberInfo.memberID = members.id
AND memberInfo.memberID = members.id
AND businessInfo.memberID = members.id
AND studentInfo.memberID = members.id
AND memberCards.memberID = members.id"
In addition to solving that problem I'm not updating the memberInfo, businessInfo and studentInfo panels when changes are made, I'm adding a new field.
When I'm just tying the member to the memberInfo a simple left join will show me just the most recent memberInfo but for easy reporting and management purposes I need to create a single table from a Select statement or query that shows me all members and links the most recent memberInfo, businessInfo, studentInfo and memberCard tables together so we can generate a table that shows everything without leaving out members that are missing data in one or more of the tables.
Any ideas? Did I build a monster?