Count associations as virtual parameter
Posted: Fri Feb 19, 2010 5:41 pm
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:
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:
procedure_references being the result of COUNT of the procedures that associate with the sequence
Cheers,
Alex
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[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_referencesCheers,
Alex