Select from two tables without repeating all the values

Question

Let me clarify myself, my question is "How to select rows from binding single tables and a cross reference table without repeating values?"

Right now I have three tables, a cross reference table and two single tables:

           Table jobs
╔══════════╦══════════════════════════╗
║ job_id   ║  details                 ║
╠══════════╬══════════════════════════╣
║  1       ║  Looking for hire...     ║
║  2       ║  We need help!...        ║
║  3       ║  We are a store that...  ║
║  4       ║  Help wanted...          ║
╚══════════╩══════════════════════════╝

      Table job2pos
╔═══════════╦═════════════╗
║  job_id   ║ position_id ║
╠═══════════╬═════════════╣
║  1        ║  10         ║
║  2        ║  10         ║
║  2        ║  12         ║
║  3        ║  11         ║
║  3        ║  13         ║
║  4        ║  10         ║
╚═══════════╩═════════════╝

        Table positions
╔═══════════════╦═══════════════╗
║ position_id   ║ position_name ║
╠═══════════════╬═══════════════╣
║  10           ║  waitress     ║
║  11           ║  cashier      ║
║  12           ║  cook         ║
║  13           ║  chef         ║
╚═══════════════╩═══════════════╝

When I perform this query:

$sql = "SELECT jobs.details, positions.name AS position FROM jobs
INNER JOIN job2pos ON jobs.job_id = job2pos.job_id
INNER JOIN positions ON job2pos.position_id = positions.position_id
WHERE job2pos.job_id IN (2)";
...
print_r($stmt->fetchAll(\PDO::FETCH_ASSOC));

And I get the following:

Array(
  [0] => Array ([details] => We need help!...
                [position] => Waitress)
  [1] => Array ([details] => We need help!...
                [position] => Cook)
)

Now I got 2 rows for the same job, but what I want is something similar to this:

Array(
  [0] => Array ([details] => We need help!...
                [position] => Array ([0] => Waitress
                                     [1] => Cook)
               )
)

If you could point out some unnecessary code I have in my code that would be great.

You might have to break your query into 2 separate queries. It is working the way it should be. — Maximus2012, Nov 15 '16 at 22:59
Try to select the job details first (for id 2) and then as you iterate through the resultset of that query using `foreach` or `while` try to select all positions that match that job. — Maximus2012, Nov 15 '16 at 23:01
Look at the accepted answer here: http://stackoverflow.com/questions/17947131/php-mysql-nested-queries for the case: `PHP to get nested list without 'complicated' queries:` — Maximus2012, Nov 15 '16 at 23:03
AFAIK, you can't get the same exact array format you desire with only mysql, since you need all the position field values, which must come from the query result along with a `details` field value as the results come in rows. (details,position) pairs — Accountant م, Nov 15 '16 at 23:07
@Maximus2012 The last bit of accepted answer? I'll try to implement that. — Sarah, Nov 15 '16 at 23:15
@Maximus2012 After doing some research online, and trying a few things out, your suggestion is probably the best I can find right now. — Sarah, Nov 16 '16 at 05:05
Could group by the value that duplicates and [`GROUP_CONCAT`](http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat) the values that vary, although in all honesty, a JSON database is probably better suited to hold these sort of data. — apokryfos, Nov 16 '16 at 07:04

score 1 · Answer 1 · edited May 23 '17 at 11:53

What you describe is not possible using only one sql-statement. You can think of a result as it would be a table. You can not have any nested information in plain sql.

We have the option of nosql databases to store and retrieve objects.

However, I think what you realy want to have is selecting multiple statements with one call.

From the mysqli documentation:

MySQL optionally allows having multiple statements in one statement string. Sending multiple statements at once reduces client-server round trips but requires special handling.

Multiple statements or multi queries must be executed with mysqli_multi_query(). The individual statements of the statement string are separated by semicolon. Then, all result sets returned by the executed statements must be fetched.

The MySQL server allows having statements that do return result sets and statements that do not return result sets in one multiple statement.

For PDO from this Q&A:

$stmt   = $db->query("SELECT 1; SELECT 2;");
$stmt->nextRowset(); //Move to next statement result
var_dump( $stmt->fetchAll(PDO::FETCH_ASSOC) ); //SELCT 2 result

Select from two tables without repeating all the values

1 Answers1