Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    Combining multiple tables

    I have this membership site which has several membership tables. The tables are as follows.

    members
    memberInfo
    businessInfo
    studentInfo
    memberCards

    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:
    PHP Code:
    "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" 
    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.

    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?
    Last edited by rgEffects; 01-29-2014 at 10:34 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Left/Right joins would solve the problems where multiplicity is formed as 0...*. Inners would be used where the multiplicity is 1...*.
    I believe you'll need some nested selects to do what you want with seeking "newest", but I'll move it to the mysql forum (mysql I assume) anyway.

    You should describe this:
    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
    Typically if you are adding a new field, there's something very wrong with your design structure.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    rgEffects (01-30-2014)

  • #3
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    Typically if you are adding a new field, there's something very wrong with your design structure.
    What I meant was new row, not new field. Maintaining a complete audit trail for user and business information is critical to the business plan and accounting requirements. Maybe I should do an insert in a new table called memberInfoHistory and an update to memberInfo at the same time. That would maintain an audit trail and keep the combined tables problem easier to manage.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Oh yeah, then that's fine. New rows are not a problem.
    Although if you have multiple records within a single table and you want to only want the "newest", you'll need to find a way to define the newest. Logically a "greater" autoincrement number would be sufficient.
    I won't attempt to write that, I'm terrible enough with aggregates let alone grabbing the max in a join. The SQL guys can probably off the cuff it :P
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,121
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    Let's say we want a list of all members along with the latest memberInfo, no matter how many memberInfo matches there are (zero to N).
    Code:
    SELECT ...explicit list of fields...
    FROM members AS M
    LEFT JOIN (
        SELECT memberID, MAX(memberInfoId) AS maxid
        FROM memberInfo
        GROUP BY memberID ) AS MI1 
    ON M.memberID =  MI1.maxid
    LEFT JOIN memberInfo AS MI2 ON MI1.maxid = MI2.memberID
    The tables are assumed to be
    Code:
    CREATE TABLE Members (
        memberID INT AUTO_INCREMENT PRIMARY KEY,
        ...
    ) ENGINE INNODB;
    CREATE TABLE memberInfo (
        memberInfoId INT AUTO_INCREMENT PRIARY KEY,
        memberID INT,
        CONSTRAINT FOREIGN KEY (memberID) REFERENCES Members(MemberID)
    ) ENGINE INNODB;
    So for each additional such table you include (which may have zero to N matches) you need to add a dummy sub-select to choose the "max" value first and only then can you join to the table *again* to get the rest of the data.
    Last edited by Old Pedant; 01-30-2014 at 07:33 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    rgEffects (02-01-2014)

  • #6
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    Thanks for pointing me in the right direction... I'll let you know how it turns out.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •