advanced mysql query(or atleast I think so)

[renierdbruyn]

my goal is to list each job_title or job_id and under each job_title or job_id I want to list the candidates that qualify for that job
I have this sql query:

SELECT * 
FROM rank_results AS result
WHERE result.job_id='{$job_id}'
GROUP BY job_id

ORDER BY rank ASC

I know this is a partial query, but I do not know how or where to do the subquery. I have tried:

SELECT * 
FROM rank_results AS result
WHERE result.job_id=( SELECT advert_id from job_advert WHERE advert_id = '{$job_id}' AND (SELECT firstname,id_number FROM    applicant_details where id_number = applicant_id) 
GROUP BY job_id    
ORDER BY rank ASC

with no luck... I have looked at Hierarchical Data in MySQL, and I think that is what I need to do. I do not kno how to accoplish this thought. any help would be appreciated

[diafol]

For this you just need a join and then use PHP to format the output. However, there are many ways to do this. You could even use a GROUP_CONCAT in order to get a comma-separated list of candidates in each record (unique job_id).

Your table/field names are difficult to follow. If you could paste a list of tables with their fields and highlight the ForeignKeys (or fields that are joined)?

[pp0007]

http://prashantmantra.com/Sql_Subquery.php

[renierdbruyn]

Thank you for your response.
Table structure: bold is primary key and italic is foreign key

rank_reslts(id, applicant_id, job_id,rank)

job_advert(advert_id, job_title, job_description, job_duties, advert_id, job_title, job_description, job_duties, start_date, end_date, salary_offered, salary_type, negotiable, contract_type, company_location, required_experience, industry_type, qualification_name, qualification_type, drivers_licence)

there are many fields in my job_advert table, but the only ones applicable to this one would be advert_id and maybe job_title(to display which job the job_id matches).

the applicant table(it is a view):

applicant_details(age,id_number, job_experience,industry_type, qualification_type, phone_number, first_name, city, relocate, minimum_salary, preferred_salary,license, email_address)

I hope that this is clear.

[diafol]

Sorry ren, due to personal reasons, I won't be able to give a proper reply until tomorrow.

[renierdbruyn]

Its okay :), thanks and Good luck!

[diafol]

Maybe...

SELECT r.rank, a.advert_id AS job_id, a.job_title, d.first_name, d.id_number AS app_id
    FROM rank_results AS r 
        INNER JOIN job_advert AS a 
            ON r.job_id = a.advert_id 
        INNER JOIN applicant_details AS d 
            ON r.applicant_id = d.id_number
    WHERE a.advert_id = ?        
    ORDER BY r.rank, d.first_name

Output: rank | job_id | job_title | first_name | app_id

I added the WHERE clause in case you needed to filter just one job, or you could use start_date and end_date in the clause.
Anyway if you have multiple jobs in the recordset, you could do something like this. I'm assuming that you're using mysql, so I'll use the while method...

$job_id = 0;
while($data = mysql_fetch_assoc($result))
{
    if($job_id != $data['job_id'])
    {
        echo "<h3>#{$data['job_id']} - {$data['job_title']}</h3>";
    }
    echo "{$data['rank']} - {$data['first_name']} ({$data['app_id']})<br />";
    $job_id = $data['job_id'];
}

That's some pretty horrible html formatting, but I hope you get the idea.

[renierdbruyn]

thanks :) it works. your blog is awesome btw...

[diafol]

Aw shucks - you sweet talker you :)