Page 1 of 1

Count associations as virtual parameter

Posted: Fri Feb 19, 2010 5:41 pm
by alex.barylski
You know how you can do things like COUNT(*) AS cnt

I have a SQL query that pulls on a 'sequences' table, simple one table query. Sequences belong to 'procedures' table records, on a many to many basis using a junction table.

here are the tables:

Code: Select all

procedures:
pkid, date, comment
 
sequences:
pkid, user, description
 
procedure_sequences
pkid, procedure_pkid, sequence_pkid
I query sequences

[sql]SELECT * FROM sequences[/sql]

I think I want to RIGHT JOIN to the procedure_sequences table to determine how many procedures reference the sequence, something like???

[sql]SELECT * FROM sequences RIGHT JOIN procedure_sequences ON sequences.pkid = procedure_sequences.pkid[/sql]

Only I want the number of associations as a virtual field added to my resultset, something like:

Code: Select all

sequences:
pkid, user, description, procedure_references
procedure_references being the result of COUNT of the procedures that associate with the sequence

Cheers,
Alex

Re: Count associations as virtual parameter

Posted: Fri Feb 19, 2010 7:36 pm
by VladSun
I think you need a LEFT JOIN.

Both LEFT and RIGHT JOINS are OUTER JOINS - you can use any of them:

tableA LEFT JOIN tableB === tableB RIGHT JOIN tableA

You may wish to benchmark these:
[sql]SELECT   sequences.pkid,   sequences.user,   sequences.description,   count(procedure_sequences.pkid) AS procedure_references
FROM   sequences
LEFT JOIN   procedure_sequences ON sequences.pkid = procedure_sequences.pkidG
ROUP BY   sequences.pkid,   sequences.user,   sequences.description[/sql]

and

[sql]SELECT   sequences.pkid,   sequences.user,   sequences.description,   (    SELECT       count(procedure_sequences.pkid)   
FROM      procedure_sequences   
WHERE      sequences.pkid = procedure_sequences.pkid   ) AS procedure_references
FROM   sequences [/sql]

Re: Count associations as virtual parameter

Posted: Mon Feb 22, 2010 8:05 am
by alex.barylski
thanks VladSun :)