CODEWITHSUNDEEP
phpmysqljoin
Mokkun
Mokkun

Reputation: 726

Need help about joining tables

I have a MySQL database and I'm trying to create a web interface to manage tickets, right now I'm trying to list the tickets like so: [title][name of the person that created the ticket][priority][date created][peoples that are in charge of this ticket]

so I have a table named tickets with the title, the id of the person that created the ticket, the priority, the date.

I have another table named users where you can find the first and last name and some other informations with their ID (you can link the two tables with that ID)

I have another table named tickets_users where you can find the ID of the peoples that are in charge of the tickets

My problem is I don't know how to link all of this in one request, it would be simple if only one people was in charge of a ticket but there can be multiple persons, I tried some queries but I always get tickets titles etc in double when there is more that one people in charge of a ticket.

Thanks in advance EDIT Example of the tables:

tickets:
   -id = 523 | title = help with internet explorer | priority = 3 | date = 2013-10-10 11:20:51
users: 
   -id = 25 | firstname = John | lastname = Light
   -id = 35 | firstname = Dwight | lastname = Night
   -id = 53 | firstname = Maria | lastname = Sun
tickets_users :
   -ticketid = 523 | userid = 25 | type = 1
   -ticketid = 523 | userid = 35 | type = 2
   -ticketid = 523 | userid = 53 | type = 2

And I'd like to be able to do a request to display:

[help with internet explorer][John Light][3][2013-10-10 11:20:51][Maria Sun - Dwight Night]

In one line (per ticket) and for all the tickets in my DB

Upvotes: 1

Views: 108

Answers (2)

Swapnil Patil
Swapnil Patil

Reputation: 979

I worked on problem and get the expected result.

select t.title,
    group_concat(
        case when tu.type = 1 then 
        concat(u.firstname, ' ', u.lastname)
        end) as creator,
    t.priority,
    t.date,
    group_concat(
        case when tu.type = 2 then 
        concat(u.firstname, ' ', u.lastname)
        end SEPARATOR ' - ') as users
from tickets t
inner join tickets_users tu on t.id=tu.ticketid
inner join users u on u.id=tu.userid  
where t.id=523;

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116120

You can use the group_concat aggregate function to group the names of the linked persons into a single field in the result. Since I don't have your exact table structure, I've made up the names of the fields and tables.

select
  t.title,
  group_concat(
    case when tu.type = 1 then 
      concat(u.firstname, ' ', u.lastname)
    end) as creator,
  t.priority,
  t.date,
  group_concat(
    case when tu.type = 2 then 
      concat(u.firstname, ' ', u.lastname)
    end SEPARATOR ' - ') as users
from
  tickets t
  inner join tickets_users tu on tu.ticketid = t.id
  inner join users u on u.id = tu.userid
group by
  t.id;

If there is indeed only one creator for a ticket (which makes sense), then I would give ticket a creatoruserid to refer to John. In that case, John doesn't need to be in the junction table, and you actually don't need the type column any more.

Upvotes: 1

Related Questions