Databases
StackOverflow2013
Postsdbo
POMySQL join multiple rows from one table while selecting only a single row from the others
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL join multiple rows from one table while selecting only a single row from the others
    primarykey
    data
    text
    <p>Maybe a bit of a strange title description, but i basically want to achieve something the <code>GROUP_CONCAT()</code> function does, only then keep the double entries.</p> <p>I have four tables i want to join, <code>client</code>, <code>doctor</code>, <code>physio</code> and <code>records</code></p> <p>Depending on the variable <code>$client</code> i want to get the client details, attending doctor and therapist (one single row from three tables) and join <em>all</em> records for that user. </p> <p>Say that in this case the <code>$client = 1</code>. The <code>records</code> table has five records where the column <code>r_client_id = 1</code>. If i run a query like below i only get one record from the <code>records</code> table, namely the first occurrence where <code>r_client_id = 1</code> (which makes sense of course):</p> <pre><code> SELECT client.c_id, client.c_name doctor.d_name, physio.p_name, records.r_record FROM adm_clients AS client INNER JOIN norm_client_doctor AS ncd ON ncd.ncd_client_id = client.c_id INNER JOIN adm_doctor AS doctor ON doctor.d_id = ncd.ncd_doctor_id INNER JOIN norm_client_physio AS ncp ON ncp.ncp_client_id = client.c_id INNER JOIN adm_physio AS physio ON physio.p_id = ncp.ncp_physio_id LEFT JOIN adm_doctor_records AS records ON records.r_client_id = client.c_id WHERE client.c_id = '".$client."' </code></pre> <p>Now assume the five records where <code>r_client_id = 1</code> are like so:</p> <p><pre><code> +------+-------------+-------------------+----------+ | r_id | r_client_id | r_record | r_date | +------+-------------+-------------------+----------+ | 1 | 1 | regular visit | 10/10/12 | +------+-------------+-------------------+----------+ | 3 | 1 | emergency control | 24/10/12 | +------+-------------+-------------------+----------+ | 7 | 1 | regular visit | 08/09/12 | +------+-------------+-------------------+----------+ | 18 | 1 | delivery | 03/01/12 | +------+-------------+-------------------+----------+ | 20 | 1 | health checkup | 10/12/11 | +------+-------------+-------------------+----------+ </pre></code></p> <p>I want my output to be in an array like so:</p> <pre><code> Client 1 - Name Doctor - Name Physio Records - Emergency control, 24/10/12 - Regular visit, 10/10/12 - Regular visit, 08/09/12 - Delivery, 03/01/12 - Health checkup, 10/12/11 </code></pre> <p>The closest one i can image is a to add a <code>GROUP_CONCAT()</code> on the records, but that, of course, groups the 'regular visit', so i'll get 4 rows instead of 5</p> <pre><code> GROUP_CONCAT(DISTINCT records.r_record SEPARATOR '|') [..] echo(str_replace("|","&lt;br>",$show->r_record)); </code></pre> <p>Anybody an idea how to display <em>all</em> the matching records? I have the feeling i'm close, but i'm out of options by now..</p> <p><strong>Edit:</strong> I forgot to mention that when i remove the <code>DISTINCT</code>, it displays all the records twice..</p> <p><strong>SOLVED:</strong></p> <p>Got it working like so:</p> <pre><code> GROUP_CONCAT(DISTINCT CONCAT (records.r_date, '~', records.r_record, '~', records.r_paraph) SEPARATOR '|') AS clientDoctorRecords, </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMaurice
    1. USMaurice
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL join multiple rows from one table while selecting only a single row from the others
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo why not group by client_id rather than record? Also you can use individual group concats.
      singulars
      1. POMySQL join multiple rows from one table while selecting only a single row from the others
      1. USnawfal
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload