SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: Join issue...

  1. #1
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join issue...

    Hey all, need some help with my join... What I have is a very small ticket system for my techs and users.

    I can pull the tickets and the reply, but what im having an issue with is that the reply doesnt know if it is a member or a tech that replied, it just shows the reply.

    (I am doing the layout in PHP, which works)

    What I want is something like:

    Subject: PC locked up
    Post: My pc is locked up and it wont do anything, etc...

    -------
    Solution [1]
    Tech reply: do this and that....

    -------
    reply: that didnt fix it

    -------
    Solution [2]
    Tech Reply: how about this....




    I have as tables:
    Members (id, firstname, lastname, email, tid(techid), etc..)
    ticket (id, mid(memberid), subject, post, time_stamp)
    ticket_reply (id, ticket_id, mid(memberid), tid(techid), reply_post, time_stamp)
    techs(id, mid(memberid))

    Code:
    $query = "
    SELECT
     ticket.mid AS T_MemberID,
       ticket.subject, 
       ticket.post, 
       ticket.time_stamp AS Post_Time,
       ticket_reply.reply_post,
       ticket_reply.time_stamp AS Reply_Time,
       ticket_reply.mid AS TR_MemberID,
       ticket_reply.tid AS TechID,
       ticket_reply.time_stamp AS ReplyTime
    FROM
       ticket 
    INNER JOIN
       ticket_reply 
    ON 
     ticket.id = ticket_reply.ticketid
    INNER JOIN
       members 
    ON 
     ticket.mid = members.id
    INNER JOIN
       tech
    ON 
     ticket_reply.tid = tech.mid
    WHERE
     ticket.id = 1";
    so what I am trying to get is if a reply is made, was it made from a tech or a member?

    in the members table, I have a field for tid(tech id) if a user is a tech, the id from techs table is put there and that members.id is put into the techs.mid field, so how do I tell mysql to see if a members id is in the techs table to show that reply as a tech and not a member?

    Are my tables not set up correctly for this? if you have any ideas that would help, please let me know.

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    for the member and tech tables, you want LEFT OUTER JOIN, not INNER JOIN. and if you want to list tickets without replies, you also need to change that to a LEFT OUTER JOIN.

    then add this line to your field list:
    Code:
    CASE
      WHEN members.id IS NOT NULL THEN 'member'
      WHEN tech.mid IS NOT NULL then 'tech'
    END AS Reply_Type
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SitePoint Zealot Gman's Avatar
    Join Date
    Jan 2002
    Location
    Sarasota, FL
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, you got me going, thank you.

    I did make a mistake though, sorry...

    instead of saying if it is a tech or user, I need to get the members alias name from the members table.

    so it would be:

    Post by: John

    Reply by: Jane

    with what I have right now, its making them all John, I think I see why it's doing that, but from searching MySQL and here, I cant see what to add to grab each posters alias name...

    Heres what I have now:
    Code:
    $query = "
    SELECT
       ticket.*,    
       ticket.mid AS T_MemberID,
       ticket.subject, 
       ticket.post, 
       ticket.time_stamp AS Post_Time,
       ticket_reply.reply_post,
       ticket_reply.time_stamp AS Reply_Time,
       ticket_reply.mid AS TR_MemberID,
       ticket_reply.tid AS TechID,
       ticket_reply.time_stamp AS ReplyTime,
       members.alias
    FROM
       ticket 
    INNER JOIN
       ticket_reply 
    ON 
     ticket.id = ticket_reply.ticket_id
    LEFT OUTER JOIN
       members 
    ON 
     ticket.mid = members.id
    LEFT OUTER JOIN
       tech
    ON 
     ticket_reply.tid = tech.mid
    WHERE
     ticket.id = 1";
    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
  •