What will be the Query : MySql Join

[akvlko]

Hey Friends!

I have some tables by which i have to select the data to display in search result.

Table Details

user_type Table

int id
string user_type

ticket Table

int id 11 not_null primary_key auto_increment
int user_id 11 not_null multiple_key
int type_id 11 not_null multiple_key
string ticket_type 100
string ticket_priority 20 not_null
blob ticket_subject 65535 not_null blob
blob ticket_desc 65535 not_null blob
datetime ticket_date 19 not_null binary
int ticket_status 2

ticket_history table

int id 11 not_null primary_key auto_increment
int user_id 11 not_null multiple_key
int type_id 11 not_null multiple_key
int ticket_id 11 not_null multiple_key
blob ticket_note 65535 blob
datetime date_modified 19 not_null binary
int ticket_status 2

and User Table

int id 11 not_null primary_key auto_increment
string full_name 200 not_null
string email 200 not_null
string password 45 not_null
datetime registration_date 19 not_null binary
int refered_user_id 11 not_null
string location 255
string address 255
string postal_code 15
string company_name 255
string company_location 255
string company_address 255
string company_logo 255
int ban 2
string country 255
string state 100

I want to Select and display the information

from ticket table{id,postdate,status,priority}
user table {full_name}
from history Table{date(the last entry for that ticket_id), status(for that Last entry for that id)}
from user_type Table{user_type}
Status {from ticket_history Table}

is there anyone who can tell me how to perform this join

and get the result

Thanks is advanced...!

[rch1231]

Hello,

I can see a couple of ways to do this but some questions first.

Does a ticket always have an entry in the ticket_history table?

In your description of the output you reference the status three times. Twice from ticket_history and once from ticket. Is that required?

Is this just for one ticket id at a time or will it be a report of all tickets?

[akvlko]

No When the Ticket was processed it will enter into the database with the user info and date of processing of that user who process on that Ticket

[akvlko]

its just due to mistakes i can update the status of ticket in the ticket table and get the status from this table every time even a ticket get modified or processed.