Advance Query Question

[masterlayouts]

I have the following database scenario:

 

companies
-------
companyID (PK)
CompanyName

jobs
-------
jobID (PK)
companyID (FK)
description

domains
-----
domainsID (PK)
jobID (FK)
domains ('retail', 'it, 'management'...etc.)

benefits
------
benefitsID (PK)
jobID (FK)
benefits ('free transport', 'health insurance'...etc.)

 

Each job in jobs is posted by 1 company only, here I would like to limit the result to 1.

Each job may have multiple domains.

Each job may have multiple benefits.

 

What would be the easiest way to retrieve the information for a specific job in jobs table? The name of the company from companies table, all the domains associated with the jobID in jobs and all benefits associated with the same jobID in benefits table?

 

What I would like to obtain (using variable named as columns in the tables) is a multidimensional array like:

 

[jobID] array =>
0 => string ['description']
1 => string ['companyName']
2 => array [benefits] =>
 0 => string 'some benefits'
 1 => string 'another benefit'
 ... so on for all benefits associated to that jobID
3 => array [domains] =>
 0 => string 'some domain'
 1 => string 'another domain'
 ... so on for all domains associated to that jobID

 

What gives me problems is the fact that one table has 1 record only and I would like to see it enforced, where the domains and benefits table have a variable number of rows. If I join all I will have a number of records equal to 1 + number of domains with that jobID + number of benefits with that jobID. I can extract the information the way I want it, still I guess I am making a mistake and I have the feeling that it may be a better way to achieve the same result.

 

What query is best fitted to this situation?

Is there a better solution (maybe using views) for this complex example?

[markifornia]

my first guess or place to tinker with is by using INNER JOIN in your query by selecting the jobs table and using the WHERE clause specify which id you want.

[Antonio Conte]

Short answer now. Maybe i'll update later.

 

I would just create an associative array with what you need then run several queries to fetch the data. All this data is bases on WHERE clauses where you query one user Id.

 

$user = array(

"id" => $ id,

"bla" => $bla,

"jobs" => $jobs-array, // query two

"images" => $image-array,

);

 

Once this works, feel free to improve upon it but making sure it works is the most important thing. Don't get hang up in getting it perfect the first time around.

 

Writing on phone btw. Sorry for the short code example.

[masterlayouts]

It looks like in a relational database there is no easy way to make it. You either go with the big query collecting all data in one query, but having this way columns with duplicated data or running several queries, this way you will not have duplicate data retrieved, but making several trips to the database will slow things a little. Depending of what we want we have to chose one of those (or take the second and using UNION to make just one trip to the database). I hoped for an easier way and it looks like it is where databases may handle hierarchical relations, which MySQL cannot do.