Count associations as virtual parameter

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Count associations as virtual parameter

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Count associations as virtual parameter

Post 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]
Last edited by VladSun on Thu Sep 09, 2010 4:57 pm, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Count associations as virtual parameter

Post by alex.barylski »

thanks VladSun :)
Post Reply