 family_id |                       name                       | abbrev  | description
-----------+--------------------------------------------------+---------+-------------
         1 | Acyl-CoA Synthetase Long-Chain                   | ACSL    |
         2 | A Disintegrin and Metalloprotease                | ADAM    |
         3 | Dischevelled                                     | DVL     |
         4 | Fibroblast Growth Factor                         | FGF     |
         5 | Forkhead Box                                     | FOX     |
         6 | GATA Binding Protein                             | GATA    |
         7 | Kinase                                           | Kinase  |
         8 | Kinesin                                          | Kinesin |
         9 | Killer Cell Immunoglobulin-like Receptor         | KIR     |
        10 | Laminin                                          | Laminin |
        11 | Myosin                                           | Myosin  |
        12 | Notch                                            | Notch   |
        13 | Phosphodiesterases                               | PDE     |
        14 | Semaphoin                                        | SEMA    |
        15 | T-Box                                            | Tbox    |
        16 | Tumor Necrosis Factor                            | TNF     |
        17 | Tumor Necrosis Factor Receptor                   | TNFR    |
        18 | Tumor Necrosis Factor Receptor Associated Factor | TRAF    |
        19 | Ubiquitin Specific Protease                      | USP     |
        20 | Wingless-related MMTV Integration Site           | WNT     |


 fam_set_id |        name         |                                          description
------------+---------------------+-------------------------------------------------------------------------
          1 | plos_20             | Original 20 families in PLoS paper, indexed to Swissprot 50.1
          2 | ppod_20             | Original 20 families, indexed to PPOD (5 May 2009).  *Likely to contain
          3 | hgnc_apr10          | All downloaded HGNC families, indexed to PPOD (5 May 2009) complete data
          4 | hgnc_apr10_selected | Selected HGNC families, indexed to PPOD (5 May 2009) complete dataset
          5 | ppod_20_human       | Original 20 families, indexed to PPOD.  Only human sequences.
          6 | ppod_20_clean       | Original 20 families, indexed to PPOD (5 May 2009). Missing annotations


-- all domains in the family
SELECT 
family_id, seq_id, hmm_acc, hmm_name
FROM family_member 
JOIN prot_seq_pfam USING (seq_id)
where fam_set_id=6 
and family_id=10;

SELECT 
hmm_acc, hmm_name, count(seq_id)
FROM family_member 
JOIN prot_seq_pfam USING (seq_id)
WHERE fam_set_id=6 
AND family_id=2
GROUP BY hmm_acc, hmm_name;

-- all sequences with a given domain, left joined to sequences in the family
SELECT prot_seq_set_member.seq_id, primary_acc, hmm_acc, 
hmm_name, family_id, abbrev, tax_id, descr
FROM prot_seq_set_member
JOIN prot_seq_pfam USING (seq_id)
JOIN prot_seq_taxonomy USING (seq_id)
JOIN prot_seq_version USING (seq_id)
LEFT JOIN fa_annotate USING (seq_id)
LEFT JOIN family_member ON (fam_set_id=6 
                            --AND family_id=10 
                            AND family_member.seq_id=prot_seq_set_member.seq_id)
LEFT JOIN family USING (family_id)
WHERE set_id=109
AND hmm_acc='PF00053.17' -- Laminin_EGF
GROUP BY prot_seq_set_member.seq_id, primary_acc, hmm_acc, 
hmm_name, family_id, abbrev, tax_id, descr;

--------------------
-- Laminin:
--------------------
23 M/H sequences

  hmm_acc   |  hmm_name   | count       total sequences in set
------------+-------------+-------
 PF00052.11 | Laminin_B   |    14       16
 PF00053.17 | Laminin_EGF |    23       64
 PF00054.16 | Laminin_G_1 |     5       22
 PF00055.10 | Laminin_N   |    19       30
 PF02210.17 | Laminin_G_2 |     9       78
 PF06008.7  | Laminin_I   |    10       10
 PF06009.5  | Laminin_II  |    10       10
(7 rows)

-------------------
-- ADAM
-------------------
61 M/H sequences

  hmm_acc   |    hmm_name     | count
------------+-----------------+-------
 PF01562.12 | Pep_M12B_propep |    59
 PF07974.6  | EGF_2           |     2
 PF08516.5  | ADAM_CR         |    53
 PF00200.16 | Disintegrin     |    60
 PF01421.12 | Reprolysin      |    58


