Mysql – How to write a query for picking 20 items from six tables with two conditions

MySQLPHP

I have six tables.

candidate,candidate_joborder,company,extra_field,joborder,user

I want to pick up 20 items from these tables with query those items are as follows.

name from company table.
title,city from joborder table.
status from candidate_joborder.
candidate_id,first_name,phone_cell,city,source,notes,current_employer,date_created,email1,desierd_pay,current_pay from candidate table.
user_name from user table.

Where as extra_fields i want to pick up 

total_experiance,relevent_experiance,notice_period,education

These are user entered data i am picking up using this query.

SELECT
    T.data_item_id,
    MAX(CASE WHEN T.field_name = 'Education' THEN value END) AS Education,
    MAX(CASE WHEN T.field_name = 'Contract or Permanent' THEN value END) AS ContractOrPermanent,
    MAX(CASE WHEN T.field_name = 'Notice period' THEN value END) AS Noticeperiod,
    MAX(CASE WHEN T.field_name = 'Total Experience' THEN value END) AS TotalExperience,
    MAX(CASE WHEN T.field_name = 'Relevant Experience' THEN value END) AS RelevantExperience
FROM
    extra_field AS T
WHERE
    T.data_item_id = 1219 -- Your filter here
GROUP BY
    T.data_item_id

And the where condition is this.

WHERE entered_by = $user_id AND date_created LIKE '$date%'

In these tables.

extra_field.data_item_id = candidate.candidate_id
candidate.entered_by = user.user_id
candidate_joborder.candidate_id=candidate.candidate_id
joborder.companycompany_id=company.company_id
joborder.entered_by=user.user_id
company.company_id=joborder.company_id

And the fiddle is here.
https://www.db-fiddle.com/f/gWr5g2222yAE8Fh1Gc6Awf/5

Best Answer

Finally i made the query.

SELECT can.date_created,jobo.title,can.first_name,can.phone_cell,can.email1,can.current_employer,comp.name,canstatus.short_description,can.notes,can.city,jobo.city,extra.TotalExperience,extra.RelevantExperience,can.current_pay,can.desired_pay,extra.Noticeperiod,extra.Education,can.candidate_id
FROM candidate AS can 
LEFT JOIN candidate_joborder AS canjob 
ON can.candidate_id = canjob.candidate_id 
LEFT JOIN joborder AS jobo 
ON jobo.joborder_id = canjob.joborder_id 
LEFT JOIN company AS comp 
ON comp.company_id = jobo.company_id 
LEFT JOIN candidate_joborder_status canstatus 
ON canjob.status = canstatus.candidate_joborder_status_id 
LEFT JOIN ( 
 SELECT T.data_item_id, 
    MAX(CASE WHEN T.field_name = 'Education' THEN value END) AS Education,    
    MAX(CASE WHEN T.field_name = 'Contract or Permanent' THEN value END) AS ContractOrPermanent, 
    MAX(CASE WHEN T.field_name = 'Notice period' THEN value END) AS Noticeperiod, 
    MAX(CASE WHEN T.field_name = 'Total Experience' THEN value END) AS TotalExperience, 
    MAX(CASE WHEN T.field_name = 'Relevant Experience' THEN value END) AS RelevantExperience FROM extra_field AS T 
GROUP BY
    T.data_item_id) extra 
ON extra.data_item_id = can.candidate_id
WHERE can.entered_by =$user_id can.date_created LIKE '$date%'"

Thank you for every one who helped me

Related Question