Databases
StackOverflow2013
Postsdbo
POin sql how to return single row of data from more than one row in the same table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POin sql how to return single row of data from more than one row in the same table
    primarykey
    data
    text
    <p>I have a single table of activities, some labelled 'Assessment' (type_id of 50) and some 'Counselling' (type_id of 9) with dates of the activities. I need to compare these dates to find how long people wait for counselling after assessment. The table contains rows for many people, and that is the primary key of 'id'. My problem is how to produce a result row with both the assessment details and the counselling details for the same person, so that I can compare the dates. I've tried joining the table to itself, and tried nested subqueries, I just can't fathom it. I'm using Access 2010 btw. Please forgive my stupidity, but here's an example of joining the table to itself that doesn't work, producing nothing (not surprising):</p> <p>Table looks like:</p> <pre><code>ID TYPE_ID ACTIVITY_DATE_TIME ---------------------------------- 1 9 20130411 1 v 50 v 20130511 2 9 20130511 3 9 20130511 </code></pre> <p>In the above the last two rows have only had assessment so I want to ignore them, and just work on the situation where there's both assessment and counselling 'type-id'</p> <pre><code>SELECT civicrm_activity.id, civicrm_activity.type_id, civicrm_activity.activity_date_time, civicrm_activity_1.type_id, civicrm_activity_1.activity_date_time FROM civicrm_activity INNER JOIN civicrm_activity AS civicrm_activity_1 ON civicrm_activity.id = civicrm_activity_1.id WHERE civicrm_activity.type_id=9 AND civicrm_activity_1.type_id=50; </code></pre> <p>I'm actually wondering whether this is in fact not possible to do with SQL? I hope it is possible? Thank you for your patience!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USfthiella
    1. USAndy Clark
    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
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYour query looks spot on to me. What happens when you run it?
      singulars
      1. POin sql how to return single row of data from more than one row in the same table
      1. USnurdglaw
    2. COcould you show the results without the where clause?
      singulars
      1. POin sql how to return single row of data from more than one row in the same table
      1. USLefteris E
    3. COI've now spent a little time setting this up, and have run your query and it **still** seems spot on to me. It returned the row for person 1 with the dates of assessment and counselling. If it's not behaving the way you expect, then perhaps you've made a mistake setting up the tables. I also wonder about your comment `The table contains rows for many people, and that is the primary key of 'id'` - since your example has two rows with id 1, it can't be a primary key - just flailing around here, trying to work out what problem you're encountering.
      singulars
      1. POin sql how to return single row of data from more than one row in the same table
      1. USnurdglaw
 

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