Databases
StackOverflow2013
Postsdbo
POSQL SELECT: combining and grouping data between three tables using subqueries
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL SELECT: combining and grouping data between three tables using subqueries
    primarykey
    data
    text
    <p>Sorry for a long question and not a very descriptive title, but my problem is very difficult to explain briefly.</p> <p>I have three database tables:</p> <pre><code>TABLE A: AID PK STATUS VARCHAR TABLE B: BID PK AID FK CID FK TABLE C: CID PK CREATIONTIME DATE </code></pre> <p>For each STATUS = 'OK' row in table A I want to find the corresponding row in C which has the latest creation time.</p> <p>First I can to fetch all rows from table A where STATUS = 'OK'.<br> Next I can to fetch all corresponding rows from table B.<br> But how to continue from there?</p> <p>For example:</p> <pre><code>select AID, CID from B where AID in (select AID from A where STATUS = 'OK') </code></pre> <p>could return something like:</p> <pre><code>AID, CID 1 1 2 2 2 3 3 4 4 5 4 6 </code></pre> <p>Let's say that CID 2 has later creation time than CID 3 and CID 6 is newer than CID 5. This means that the correct result would be rows 1, 2, 4 and 6 in table C.</p> <p>Is there a way to express this with a query?</p> <p>EDIT: Sorry that I wasn't specific enough. What I want to get is the CIDs from table C.</p> <p>EDIT: I counted returned rows with the different solutions. Results were very interesting - and diversified:<br> HAINSTECH: 298 473 rows<br> JMUCCHIELLO: 298 473 rows<br> RUSS CAM: 290 121 rows<br> CHRIS: 344 093 rows<br> TYRANNOSAURS: 290 119 rows</p> <p>I have not yet had the time to analyse returned rows in depth, but I'd really appreciate views on which of the queries are "broken" and why.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UStputkonen
    1. UStputkonen
    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
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL SELECT: combining and grouping data between three tables using subqueries
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf CID 2 has the same creation time as CID 3 do you want both results? If CID X has the latest creation time for multiple AIDs do you want the CID X to appear multiple times in the result list?
      singulars
      1. POSQL SELECT: combining and grouping data between three tables using subqueries
      1. USAlohci
    2. COThanks for great questions. I want to get the latest C for each A. If CID 2 and CID 3 are related to the same AID, they will never have the same creation time. Also CID X will never be associated to more than one AID. (I admit that if we could change the schema we certainly would!)
      singulars
      1. POSQL SELECT: combining and grouping data between three tables using subqueries
      1. UStputkonen
    3. COI counted returned rows with the different solutions. Results were very interesting - and diversified: HAINSTECH: 298 473 rows JMUCCHIELLO: 298 473 rows RUSS CAM: 290 121 CHRIS: 344 093 TYRANNOSAURS: 290 119 I have not yet had the time to analyse returned rows in depth, but I'd really appreciate views on which of the queries are "broken" and why.
      singulars
      1. POSQL SELECT: combining and grouping data between three tables using subqueries
      1. UStputkonen
 

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