SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    joining a table on 1 of 2 columns

    Hi there

    I'm a bit stuck on how to achieve the following:

    I have 3 tables:
    members
    members_contacts
    members_business

    members_contacts consists of:
    member_id
    contact_member_id
    approved

    member_id and contact_member_id are the primary key

    I want to retrieve a list of contacts for a given member. The members table needs to be joined in order to get the members name. I'm having trouble retrieving the correct name as the member_id to join on could either be member_id or contact_member_id depending upon who added who.

    So, somehow I need to JOIN on member_id if its not equal to the given member id or JOIN on member_contact_id if thats not equal to the given member_id.

    So far, I have this:
    Code:
    SELECT
    	m.firstname,
    	m.lastname,
    	mc.*,
    	mb.business_id,
    	mb.company
    FROM
    	members m,
    	members_contacts mc
    LEFT JOIN
    	members_business mb
    	ON mb.member_id = mc.contact_member_id
    WHERE
    (
    	mc.member_id = $memberid
    	OR
    	mc.contact_member_id = $memberid
    )
    I need to add something to the where clause above to get the correct name or perhaps something completely different. Any ideas?

    Many thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would go for the "something completely different"

    start with just two tables

    you can bring the 3rd one into the picture later

    but before you start (re)writing the SQL, ask yourself this: if todd has fred as a contact, does fred also have todd as a contact?

    if so, then you don't need the OR in your WHERE clause

    if not, then you really don't need the OR in your WHERE clause

    if this doesn't make sense, show some rows of data before we talk about the SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi there,

    If Todd has Fred as a contact then Fred can't have Todd as a contact. However, if Todd adds Fred as a contact then the rows in the database would look like this (some columns removed):
    Code:
    Members
    member_id    firstname
    1                Todd
    2                Fred
    
    Members Contacts
    member_id        contact_member_id      approved
    1                     2                              0
    But if Fred adds Todd then Members Contacts would be:
    Code:
    member_id        contact_member_id      approved
    2                     1                              0
    The reason it works like this is so we can tell who added who and so who needs to approve the contact. But maybe it is easier to have another column to indicate who initiated the contact?

    Hmmm, it might make the delete option less simple too as I'd have to delete where member_id = x and contact_member_id = y and vice versa. Perhaps it might also make sense to have an auto increment id?

    Many thanks

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2006
    Posts
    145
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello again. I've thought about this a little more and I need to keep the format as it is so that I know that members_contacts.member_id is the member that initiated the contact and members_contacts.contact_member_id is the member that received the contact request and so is the person that needs to approve the request.

    I also need to be able to order by approved and date so that contacts requiring approval are listed first and with the most recent first.

    So, I can easily do this with 2 queries, 1 for contacts I've created and 1 for contacts received. But what would be nicer is to have it done in one query order by approved and date. Not a clue on that one though :s

    Many thanks


Bookmarks

Posting Permissions

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